Can anyone say what is wrong with this code? [closed]

Question

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.

Answer 1

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

Answer 2

what is wrong with this code?

Wrong point is:

WHERE ... Relationship='Son' AND NOT Relationship='Daughter'

1. The conditions are applied to THE SAME RECORD. So when Relationship is equal to 'Son' then it is not equal 'Daughter' in this record automatically.

2. The conditions do not check any another record. So if a record where Relationship='Son' exists then SELECT will return (this) record regardless of the existence of another record for the same employee with Relationship='Daughter'.

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.

SELECT DISTINCT Fname, Lname, Address
FROM employee e
JOIN dependent d1 ON d1.essn = e.ssn
WHERE d1.Relationship = 'Son'
AND NOT EXISTS ( SELECT 1 
                 FROM dependent d2
                 WHERE d2.essn = e.ssn 
                   AND d2.relationship = 'Daughter' )