SQL where clause "up to" match

Question

I have the following two columns in a table.

Now, based on the data (ids in second columns) sent in from the client, I want to return the id from the first column as the following:

Client sends in: 13, returns 24, 25 and 26

Client sends in: 1, returns 24 and 26

Client sends in: 1 and 13, returns 24 and 26

Client sends in: 1, 12 and 13, returns 26

How can I construct a SQL query like this? I've tried with in operator, but it returns wrong result in the last case where client sent in 1, 12 and 13. It returns all of them but I want only 26 in this case.

SELECT firstId FROM MyTable WHERE secondId in(1,12,13)

Have you tried https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server or https://stackoverflow.com/questions/971964/limit-10-20-in-sql-server? — Ben Holland, Aug 16 '18 at 17:07

score 1 · Accepted Answer · answered Aug 16 '18 at 16:54

You could use:

SELECT firstId
FROM tab
WHERE secondId in (1,12,13)
GROUP BY firstId
HAVING COUNT(DISTINCT secondId) = 3; -- number of elements from WHERE

Another approach is to use INTERSECT:

SELECT firstId FROM tab WHERE secondId = 1
INTERSECT
SELECT firstId FROM tab WHERE secondId = 12
INTERSECT
SELECT firstId FROM tab WHERE secondId = 13;

SQL where clause "up to" match

1 Answers1