Question 20003 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL query to return single value matching exact values in another column of same table – Mysql

Table:  Dummy

|Colum1 | Colum2 |
|:------|:-------|
| 219   | 217    | 
| 219   | 218    | 
| 228   | 218    |
| 228   | 225    |
| 229   | 218    |
| 229   | 220    |

I need result set where if user provided numbers in where clause should exactly match along with its count example. if user sends where ( 217,218 ) then it should return with colum1 has value 219.

Following queries is returning the result set as below.

SELECT *
from Dummy
GROUP BY Colum1 
having count(distinct Colum2 ) = 2 and Colum2 in (217, 218);

| 219   | 217    |
| 228   | 218    | 
| 229   | 218    |

required output:

| 219   | 217    |

The no of distinct Colum2 is dynamic , it can be 2 ,3 ,4 or 5.

Tags:

4

Answers


  1. 0

    You could just join the table back on its self

    SELECT d1.* 
FROM Dummy d1 
JOIN dummy d2
    ON d2.Column1 = d1.Column1
    AND d2.Column2 = 218
WHERE d1.Colum2 = 217
    Login or Signup to reply.
  2. 0

    You need to move the Colum2 criterion to the WHERE clause:

    SELECT Colum1
FROM Dummy
WHERE Colum2 IN (217, 218)
GROUP BY Colum1 
HAVING COUNT(DISTINCT Colum2) = 2;

    As pointed out by Aconcagua, you could add a NOT EXISTS if you want to include only the Colum1 values which do not have any other Colum2 values:

    SELECT Colum1
FROM Dummy d
WHERE Colum2 IN (217, 218)
AND NOT EXISTS (SELECT 1 FROM Dummy WHERE Colum2 NOT IN (217, 218) AND Colum1 = d.Colum1)
GROUP BY Colum1 
HAVING COUNT(DISTINCT Colum2) = 2;

    Here’s a db<>fiddle

    Login or Signup to reply.
  3. 0

    Based on @user1191247‘s answer and assuming you want to get those groups where the list of values of column2 comprises exactly the values {217, 218} (none less, none more) then the following query can give you the desired result:

    SELECT d1.colum1
FROM dummy d1 JOIN dummy d2 ON d1.colum1 = d2.colum1
WHERE d1.colum2 in (217, 218)
GROUP BY d1.colum1 
HAVING COUNT(DISTINCT d1.colum2) = 2 AND COUNT(DISTINCT d2.colum2) = 2;

    The basic idea of is: The self-join assures we have column2 duplicated, then we can filter out all unwanted values within one of the duplicated columns, but leave them in the other. If all wanted values exist the count of the filtered column needs to equal the number of these values – and the count of the unfiltered column needs to alike, otherwise surplus values would exist (note that the latter’s count then would be greater; it cannot ever get smaller, though).

    See user1191247’s modified fiddle (only the query for two values is adjusted).

    Login or Signup to reply.
  4. 0

    Try using the below query:

    SELECT Parent_Id
FROM Parent_Child
GROUP BY Parent_Id
HAVING GROUP_CONCAT(DISTINCT Child_Id order by Child_Id asc ) = '217,218';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search