CODEWITHSUNDEEP
mysqlsqldatabase
THE DOCTOR
THE DOCTOR

Reputation: 4555

SQL Query Matching 2 Rows for 1 Value

How would I write a query to find where there is a match for 2 specific values in one column for every 1 value in the other column? I'm using a MySQL database.

For example:

| test_id | animal_type | classification_id |
----------------------------------------------
    1          cat             456
    2          dog             456
    3          mongoose        456
    4          cat             123

I would like to write a query that finds the 2 rows which happen to have a classification_id of '123' and '456' for the same animal_type wherever that occurs in the table.

If I write the following it doesn't work since I don't know how to include animal_type in this query. 

SELECT *
FROM test_table
WHERE classification_id = '123' AND classification_id = '456'

How would I write a query to select all rows that have both 123 and 456 as the classification_id for a specific animal_type?

Upvotes: 1

Views: 72

Answers (2)

Vijay Rathore
Vijay Rathore

Reputation: 603

SELECT count(test_id) as animal_count,test_table.*
FROM test_table
WHERE classification_id = '123' AND classification_id = '456' 
GROUP BY animal_type HAVING animal_count > 2

Use group by and Having.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use:

SELECT animal_type
FROM test_table
WHERE classification_id IN (123, 456)
GROUP BY animal_type
HAVING COUNT(DISTINCT classification_id) = 2;

And if you need full rows:

SELECT *
FROM test_table
WHERE animal_type IN (SELECT animal_type
                      FROM test_table
                      WHERE classification_id IN (123, 456)
                      GROUP BY animal_type
                      HAVING COUNT(DISTINCT classification_id) = 2)

Upvotes: 3

Related Questions