SQL - Using a join to pass a list to a lookup tbl

Question

Hi Everyone,

I want to be able to loop over a list and return a single row where all the list elements have matched.

e.g.

lest say my list of id's is 1,2,3 I want to write an SQL statment that will do the following (without breaking).

SELECT id1 FROM TBL WHERE 0=0 AND id2 = 1 AND id2 = 2 AND id2 = 3

Is this possible?

Thanks in advance

Answer 1

AND here makes no sense. You probably want to use OR: id2 = 1 OR id2 = 2 OR id2 = 3 or even better IN:

SELECT id1 FROM TBL WHERE id2 IN (1,2,3)

Answer 2

Do you mean that you want all id1 records for which there is a matching id2=1 and a matching id2=2 and a matching id2=3?

WITH tbl As
(
SELECT 1 AS id1, 1 AS id2 UNION ALL
SELECT 1 AS id1, 2 AS id2 UNION ALL
SELECT 1 AS id1, 3 AS id2 UNION ALL
SELECT 1 AS id1, 4 AS id2 UNION ALL
SELECT 2 AS id1, 1 AS id2 UNION ALL
SELECT 2 AS id1, 2 AS id2 UNION ALL
SELECT 3 AS id1, 1 AS id2 UNION ALL
SELECT 3 AS id1, 2 AS id2 UNION ALL
SELECT 3 AS id1, 3 AS id2
)
SELECT id1 
FROM tbl 
WHERE id2 IN (1,2,3)
GROUP BY id1
HAVING COUNT(DISTINCT id2) = 3

Returns

id1
-----------
1
3

Is that what you need?

Answer 3

A third guess, based on the title of the question - if the list of values is in a second table, the query can be written to select values from the first table based on a join to the second table - like so:

SELECT id1 FROM TBL T1
join TBL2 T2 on T1.id2 = T2.id

Of course, in this example TBL2 would have to have only the values 1, 2 and 3 in the id column.