Forum Moderators: open
id1 int 11
id2 int 11
Example data is:
id1 / id2
1 / 1
1 / 2
1 / 3
2 / 2
2 / 3
2 / 4
What I would like is to find the value of id1 where the values of id2 match a set of values (like 2, 3 AND 4).
How can I get the id1 of 1 returned given a set of values (2, 3, 4) to match against id2?
If you want to know all of the id1s where there exists a record where id2 = 2 OR 3 OR 4 then something like the following should work:
SELECT distinct id1
FROM tLookup
WHERE id2 IN (2,3,4)
From this query you should get the following result set:
id1
---
1
2
If you want to know all of the id1s where there exists a record where id2 = 2 AND id2=3 AND id2=4 then something like the following should work:
SELECT distinct id1
FROM tLookup AS L1
JOIN tLookup AS L2 ON L1.id1 = L2.id1
JOIN tLookup AS L3 ON L1.id1 = L3.id1
WHERE L1.id2=2 AND L2.id2=3 AND L3.id2=4
From this query you should get the following result set:
id1
---
2
Because there is only 1 ID1 value (X=2) that has a row of X/2, X/3, AND X/4. ID1 = 1 is missing the row 1/4 so only ID1=2 is shown in the results.
Sorry I didn't test this prior to posting. Just winged it but I think the above 2 queries and results sets are correct.
Hope that helps.
