Mysql – Can anyone say what is wrong with this code?

MySQL

SELECT Fname,Lname,Address
FROM employee
WHERE EXISTS (SELECT *
FROM dependent
WHERE Ssn=Essn AND Relationship='Son' AND NOT Relationship='Daughter')
GROUP BY Ssn

I'm trying to List the first name, last name, and address of all employees who have a son as a dependent but do not have a daughter as a dependent.

Best Answer

You can use exists and not exists

select ...
  from employee e
 where exists (
      select * from dependent where ssn = e.ssn and relationship = 'Son'
 )
 and not exists (
    select * from dependent where ssn = e.ssn and relationship = 'Daughter'
 )

You can also achieve this with a left join

select ...
  from employee e
  left join dependent d
    on e.ssn = d.ssn
   and d.relationship = 'Son'
  left join dependent d2
    on e.ssn = d2.essn
   and d2.relationship = 'Daughter'
 where d.ssn is not null
   and d2.ssn is null