CODEWITHSUNDEEP
mysqlsql
Haldir87
Haldir87

Reputation: 375

How can I check if the same ID has different values in different rows?

I have the following table:

+----+----------+-------------------+
| ID | ID_CICLO | id_caratteristica |
+----+----------+-------------------+
| 1  | 72       | 2                 |
+----+----------+-------------------+
| 2  | 72       | 3                 |
+----+----------+-------------------+
| 3  | 73       | 2                 |
+----+----------+-------------------+
| 4  | 73       | 4                 |
+----+----------+-------------------+
| 9  | 3        | 2                 |
+----+----------+-------------------+
| 10 | 3        | 4                 |
+----+----------+-------------------+

And I want to extract all IDs that have an id_caratteristica = 2 and also equal to 4.

This is what I tried, but it is wrong because the And operator only works on the same row.

SELECT * FROM caratteristiche_ciclo 
WHERE id_caratteristica = 2 
  and id_caratteristica = 4

The result should be ID_CICLO {3, 73}. How can I achieve that?

Upvotes: 1

Views: 1898

Answers (3)

Rajat
Rajat

Reputation: 5803

From you expected result, it looks like you are looking for id_ciclo, not id.

SELECT id_ciclo
FROM caratteristiche_ciclo 
WHERE id_caratteristica IN (2,4) 
GROUP BY id_ciclo
HAVING AVG(id_caratteristica) = 3

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can approach this problem using conditional aggregation based filtering. Firstly, get all the ID where id_caratteristica IN(2,4).

Then, you can use HAVING clause to filter out the ID having both the id_caratteristica values: 

SELECT id  
FROM caratteristiche_ciclo  
WHERE id_caratteristica IN (2,4) 
GROUP BY id 
HAVING COUNT(DISTINCT id_caratteristica) = 2

For good performance, you can define the following Covering Index: (id, id_caratteristica)

Upvotes: 2

Mjh
Mjh

Reputation: 2945

The query is:

SELECT ID, ID_CICLO FROM caratteristiche_ciclo WHERE id_caratteristica IN(2, 4);

If you don't get the result you expect, your data is definitely not the same as the sample you posted.

Upvotes: 1

Related Questions