MySQL: How do I retrieve entries from a table with duplicate values and that satisfy multiple condit-CodePudding

Suppose I have the following table...

id	value
1	7
1	8
1	9
2	6
2	7
2	7
3	8
3	8
3	8
4	7
4	9
4	9

I would like to extract all ID numbers that contain the values 7 and 9 (ids 1 and 4 in this case). The problem I am having is that there are duplicate values in the database. I have tried the following self-join:

SELECT
   *
FROM
   (SELECT
      id
   FROM
      table_name
   WHERE
      value = 7
   GROUP BY value) AS t1
      JOIN
   (SELECT
      id
   FROM
      table_name
   WHERE
      value = 9
   GROUP BY value) AS t2 ON t1.id = t2.id;

Which works but I feel has two issues: (1) there is a lot of wasted searching as SQL searches through the table twice*; (2) this code doesn't scale well - what if I wanted to search for the presence of three, four, five, etc. values?

Is there a better approach I could take?

*Is this true? I recently began learning SQL and come from a Python background

CodePudding user response：

This type of query comes up often here. One approach uses aggregation:

SELECT id
FROM table_name
WHERE value in (7, 9)
GROUP BY id
HAVING COUNT(DISTINCT value) = 2;

CodePudding user response：

This is better solved by a GROUP BY and a HAVING clause:

SELECT id
FROM t
WHERE value IN (7, 9)
GROUP BY id
HAVING COUNT(DISTINCT value) = 2