CODEWITHSUNDEEP
sqlms-access
fullOfQuestions
fullOfQuestions

Reputation: 453

SQL Select Rows Where Does not Exist

I have two columns, Name and Date

Name       Date
John       2/21/2014
Joe        2/21/2014
Sue        2/22/2014
Joe        2/22/2014
Steve      2/23/2014
John       2/23/2014

I need a statement that selects every Date that does NOT have a certain person.

So, for example, I need a list of dates that don't have a matching row for John, I would get just get 2/22/2014, because only Sue and Joe have records on that date.

Upvotes: 0

Views: 90

Answers (4)

Hart CO
Hart CO

Reputation: 34784

You can use a GROUP BY and HAVING:

SELECT Date
FROM Table1
GROUP BY Date
HAVING MAX(CASE WHEN Name = 'John' THEN 1 END) IS NULL

Syntax may need to be adjusted for Access.

SQL Server demo: SQL Fiddle

Upvotes: 1

D Stanley
D Stanley

Reputation: 152624

You can use NOT IN:

SELECT DISTINCT Date
FROM Table
WHERE Date NOT IN
(
   SELECT Date FROM Table where Name = 'John'
)

Upvotes: 2

Dave Mason
Dave Mason

Reputation: 4946

SELECT DISTINCT t.Date
FROM MyTable t
WHERE NOT EXISTS (
    SELECT 1
    FROM MyTable t2
    WHERE t2.Name = 'John'
    AND t2.Date = t.Date
)

Upvotes: 1

Oscar Bralo
Oscar Bralo

Reputation: 1907

Try this, maybe it helps! ;)

SELECT Date
FROM table
WHERE 'John' NOT IN Name

Upvotes: 0

Related Questions