How to filter out records
-
Hello,
I have the following table and data:
table1:
ID Col
1 term1
2 term2
table2:
ID1 ID2 Term
100 1 term1
101 2 term2
102 3 term1
103 1 term2
104 3 term2
Now, I want to select those records in table2, where the all of the two(or more) terms in table1 appear in table2.term, in the above sample data, record 100, 103 is a pair, records 102 and 104 is another pair. I want to get the result 1 and 3 (table2.ID2)
How do I write my query?
Thanks. -
hello,
i mocked up your environment from above and changed a couple of table names and fields to get you what you want. from the simple dataset, the ending query should get you to where you are looking for. hope this helps...
create
table term(term_id int, term_desc varchar(50))
create
table other_term(other_id int, pair_id int, term_id int)
insert
into term values (1, 'term1')
insert
into term values (2, 'term2')
insert
into other_term values (100, 1, 1)
insert
into other_term values (101,2, 2)
insert
into other_term values (102, 3, 1)
insert
into other_term values (103, 1, 2)
insert
into other_term values (104, 3, 2)
select
ot.other_id, ot.pair_id, t.term_desc
from
(select count(*) term_count from term) tc
inner join (
select pair_id, count(*) pair_count
from
(select distinct pair_id, term_id
from other_term) dt
group by pair_id) otc
on tc.term_count = otc.pair_count
inner join other_term ot
on otc.pair_id = ot.pair_id
inner join term t
on ot.term_id = t.term_id
order
by pair_id
-
My first question would be... what have you tried?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I have solved this problem by using adding an additional field to temp table, then increment the record that matches the term in another table, then select the record that has a value of the number of terms in that table for the additional field. That means the record appears for every term.
Thank you guys.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply