Querying sql table with multiple values

Question

I would like to query a sql table from below

ID Val
-------------
1 5
1 7
1 8
1 9
2 5
2 7
2 9
3 1
3 5

that would return the following set of results query > select distinct ID from dbo.table where val in (5,7,9)

result
--------
ID
1
2

I run into a problem where a single row can match only one val from the subset and not all of them...

i don't understand the question... there is an ID of 1, 2, and 3 associated with the Val of 5. On what condition do you want to exclude 3 from the result set? — tenfour
– tenfour, Commented Dec 21, 2010 at 19:55

Mark Byers · Accepted Answer · 2010-12-21 19:53:23Z

2

Assuming the rows are distinct:

SELECT ID
FROM your_table
WHERE Val IN (5,7,9)
GROUP BY ID
HAVING COUNT(*) = 3

answered Dec 21, 2010 at 19:53

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mko Over a year ago

Great and what about if the subset is dynamically generated?

Mark Byers Over a year ago

@John: Then you need to create the string like (5,7,9) and also update the 3 in COUNT(*) = 3 has to match the number of items. So if you have 4 items you need COUNT(*) = 4.

mko Over a year ago

Yes, that makes sense. I should probably count the params and pass it to the stored proc. What if I would use "not in" as an additional filter. Would i have to add count of those values together with previous 3 params? etc. having count(*) = 3+(not in params)

Collectives™ on Stack Overflow

Querying sql table with multiple values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related