CODEWITHSUNDEEP
sqlsql-serverfilterconditional-statements
Delfino
Delfino

Reputation: 1009

SQL -- Excluding Tuples

I'm writing a query to select employee names from a database where the employees are married but have no children.

I have two different tables: Employee and Dependents

Employee has the following fields

fname, lname, ssn

And Dependents have the following fields

essn, dependents_name, relationship

Dependents.essn is a FK that references Employee.ssn

Some Employee.ssn have multiple tuples in Department, each with a different relationship status (spouse, son, daughter), describing the type of dependent that employee has.

I'm looking to write a query that selects those employees, based on the ssn -> essn relationship, that have the relationship spouse but not the relationships son or daughter.

So far, this is what I've tried:

select e.fname, d.relationship
from (employee e left outer join dependents d
on e.ssn = d.essn)
where d.relationship = 'spouse'

It returns the tuples of employees with the spouse value, but also with the son and daughter value.

How can I filter my tables to include only those employees with the spouse value?

Upvotes: 1

Views: 698

Answers (3)

radar
radar

Reputation: 13425

you can use NOT EXISTS clause 

select e.fname, d.essn, d.relationship
from employee e 
join dependents d
on e.ssn = d.essn
and d.relationship = 'spouse'
and not exists ( select 1 from dependents d1
                 where d1.essn= e.ssn
                 and d1.relationship <> 'spouse'
)

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

Here's another way with a common table expression and count with partition:

with cte as (
  select e.fname, 
    d.relationship,
    count(d.relationship) over (partition by e.ssn) cnt
  from employee e 
    join dependents d on e.ssn = d.essn
)
select fname, relationship
from cte
where cnt = 1 and relationship = 'spouse'

BTW, no need for an OUTER JOIN -- your WHERE criteria negates it since you require a spouse to exist.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do this with aggregation and a having clause:

select e.fname
from employee e left outer join
     dependents d
     on e.ssn = d.essn
group by e.fname
having sum(case when d.relationship = 'spouse' then 1 else 0 end) > 0 and
       sum(case when d.relationship in ('son', 'daughter') then 1 else 0 end) = 0;

Upvotes: 0

Related Questions