CODEWITHSUNDEEP
sqlsql-serverselect
Paretree
Paretree

Reputation: 11

If an ID has at least one record satisfying a where clause then exclude it from the select statement

I am trying to write a SQL select query which selects only those records for which there is no entry beyond a certain date.

The only workaround I have found so far is to extract the IDs with entries past the date then create a new select statement to exclude these IDs.

To illustrate, I wanted to select only those that have not taken up a sport since 2015: problem illustration

The output of this would be Person ID 1 and Person ID 3.

A query such as 

SELECT [Person ID] WHERE [Date Taken Up] < 2015

Would return all 4 persons as they have all taken up a sport before 2014 which is not what I want.

Any help would be much appreciated - thanks in advance.

Upvotes: 1

Views: 401

Answers (3)

Ashu
Ashu

Reputation: 482

SELECT [Person ID] FROM YourTable WHERE [Date Taken Up] < 2015

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

In natural language, you want to select Person IDs except those that satisfy a given search condition. Here's a translation into SQL:

SELECT [Person ID]
  FROM YourTable 
EXCEPT
SELECT [Person ID]
  FROM YourTable 
 WHERE [Date Taken Up] < 2015;

Upvotes: 1

Lamak
Lamak

Reputation: 70648

You can use NOT EXISTS:

SELECT *
FROM dbo.YourTable A
WHERE NOT EXISTS(SELECT 1 FROM dbo.YourTable
                 WHERE [Date Taken Up] >= 2015
                 AND [Person Id] = A.[Person Id]);

Upvotes: 1

Related Questions