Bidisha Bidisha - 6 months ago 9
SQL Question

SQL select rows where col1 and col2 are same combination with different col3 values

I am kind of not an expert in SQL.
I have a table :

C1 || C2 || C3

--------------------------
abc || 123 || ADD

abc || 123 || MERGE

xyz || 456 || ADD

xyz || 456 || UPDATE


I need to get the rows which have same C1 and C2 and C3 as ADD and MERGE
What is the SQL for that?

Answer

If you want to find C1 and C2 which has only ADD and UPDATE in C3 then use this

select C1, C2 
from yourtable 
Group by C1, C2
Having count(case when C3 in ('ADD','MERGE') then 1 end) = count(*)

If you want to find C1 and C2 has both ADD and UPDATE in C3 then use this

select C1, C2 
from yourtable 
Group by C1, C2
Having count(case when C3 = 'ADD' then 1 end) > 0
   AND count(case when C3 = 'UPDATE' then 1 end) > 0