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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)

Method 2

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.

Method 3

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x