X-MEN X-MEN - 1 year ago 59
SQL Question

Query to get rows from a table depending on another column , if all column value exist

My Table has two columns, no primary key defined.

say name and salary

name salary
A 100
B 200
B 100
C 100
A 200

I want salary of A,B where fetched salary is not owned by any other name
say distinct salary fetched of A,B is 100,200
But my result will only show 200 as 100 is owned by C

Answer Source

Do a GROUP BY. Use HAVING to make sure a salary has two different names, and also no other than A and B:

select salary
from tablename
group by salary
having count(distinct name) = 2
   and count(case when name not in ('A','B') then 1 end) = 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download