sql server - SQL -- Excluding Tuples -

sql server - SQL -- Excluding Tuples -

i'm writing query select employee names database employees married have no children.

i have 2 different tables: employee , dependents

employee has next fields

fname, lname, ssn

and dependents have next fields

essn, dependents_name, relationship

dependents.essn fk references employee.ssn

some employee.ssn have multiple tuples in department, each different relationship status (spouse, son, daughter), describing type of dependent employee has.

i'm looking write query selects employees, based on ssn -> essn relationship, have relationship spouse not relationships son or daughter.

so far, i've tried:

select e.fname, d.relationship (employee e left outer bring together dependents d on e.ssn = d.essn) d.relationship = 'spouse'

it returns tuples of employees spouse value, son , daughter value.

how can filter tables include employees spouse value?

you can utilize not exists clause

select e.fname, d.essn, d.relationship employee e bring together dependents d on e.ssn = d.essn , d.relationship = 'spouse' , not exists ( select 1 dependents d1 d1.essn= e.ssn , d1.relationship <> 'spouse' )

sql sql-server filter conditional