-2
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.

closed as unclear what you're asking by mustaccio, John aka hot2use, Philᵀᴹ, McNets, John Eisbrener Oct 22 at 14:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    You should tag it with what version of mysql you are using instead of the tool you are using to write the query with. – Joe W Oct 21 at 20:37
  • Should be using <> relationship='Daughter', and not NOT, I believe. – Sean Brookins Oct 21 at 20:44
  • @SeanBrookins - I suspect they are equivalent (except maybe for NULLs.) – Rick James Oct 22 at 3:41
  • 1
    Please consider reading the following articles: How do I ask a good question? (Help Centre) and Minimal, Complete, and Verifiable Example (Meta DBA.SE). Currently your question doesn't contain enough information and might be closed as unclear what you're asking. Add some sample data and what you expect to be returned. – John aka hot2use Oct 22 at 6:26
2

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
1

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' )

Not the answer you're looking for? Browse other questions tagged or ask your own question.