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

  • Select Query With Distinct on One Field
  • Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic
  • Join and set two tables
  • SQL Server: Store a database for portability
  • inserting into a sql table, values input to some text boxes,combo boxes and a datetimepicker by csharp
  • Can't query for description from SQL using ADSI
  • 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?

    3 Solutions collect form web for “SQL — Excluding Tuples”

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

    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'
    
    • SQL Fiddle Demo

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

    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;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.