CODEWITHSUNDEEP
mysql
user2914191
user2914191

Reputation:

MySQL match values in IN clause to every group value

I have the following MySQL table:

id   value
1    a
1    b
2    b
2    c
3    c

I want to query this table and get all the ids whose group matched elements in the MySQL IN clause.

For example, if I provide IN("a", "b", "x", "z"), I want to return 1 because every row in 1 (group 1) matches the values I provided in the clause.

How can this be done?

Upvotes: 3

Views: 380

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270727

This is an interesting problem if I understood it correctly.

This approach checks the aggregate COUNT() of each id when limited by the IN () list against the total COUNT() aggregate of each id when not limited by a WHERE clause at all. If the two counts match, it is because every value associated with the id was matched in your IN () list.

SELECT DISTINCT tlimit.id 
FROM 
  ( 
     -- A subquery to get the *limited* count per id
     SELECT id, COUNT(tbl.id) as limitc
     FROM tbl
     WHERE value IN ('a','b','x','z')
     GROUP BY tbl.id
  ) tlimit 
  INNER JOIN (
     -- A subquery to get the *total* count per id
     SELECT id, COUNT(*) AS total FROM tbl GROUP BY id
  ) tcount 
    -- Join the total count per id against the limited count
    -- so the query only returns rows where they're exactly equal
    ON tlimit.id = tcount.id
    AND tlimit.limitc = tcount.total

Here it is in action, with an additional id = 4 that should be returned: http://sqlfiddle.com/#!9/c6aa0a/1

Upvotes: 2

Related Questions