CODEWITHSUNDEEP
sql
mt88
mt88

Reputation: 3015

Finding SQL Users who satisfy two different conditions

I have a table like the following:

USER ID    ||    Quality_A

200;              1

15;               3

35;               7

76;               6

200;              5

If i want to find all the users such that their exists a row where that users quality is 1, and their exists a row where that users quality is 5, how do I do so. For instance, in the table above, user 200 has two rows, and the qualities respectively would be 1 and 5. Thus he would be counted.

Also, help with what this should be titled would be greatly appreciated.

Upvotes: 1

Views: 237

Answers (2)

TroutFishing
TroutFishing

Reputation: 43

I recommend the following query: 

SELECT DISTINCT t1.User_ID 
FROM table t1 
WHERE t1.Quality_A = 1
AND EXISTS (SELECT 1 
      FROM table t5 
      WHERE t5.User_ID = t1.User_ID 
      AND t5.Quality_A = 5
      );

The idea is that we look for all User_IDs with quality equal to 1, then check the original table (using a new alias) for all User_IDs with quality equal to 5 that match one of the IDs we've already collected.

You could also get the same results using a self join, but this method may be less transparent:

SELECT DISTINCT t1.User_ID 
FROM table t1
JOIN table t5 ON (t1.User_ID = t5.User_ID)
WHERE t1.Quality_A = 1
AND t5.Quality_A = 5;

Upvotes: 2

Rahul
Rahul

Reputation: 77866

You can try like this

select user_id
from table1
where quality_a in (1,5)
group by user_id
having count(distinct quality_a) >= 2

Upvotes: 2

Related Questions