CODEWITHSUNDEEP
sql-servert-sql
Hanu
Hanu

Reputation: 55

TSQL How to select SQL records with following where condition

I have a table Test with records

ID             Date    
1            12/08/2017
1            11/10/2016
1            06/09/2010
1            06/29/2009
2            05/29/2009
2            03/29/2009
2            02/29/2009

I want to select id's where Date >2010 but I want to include all record dates I tried this 

select * from Test
where Date >='01/01/2010'

but this return only 3 records of id 1. If only all dates of id are less than 2010 then my select query should not select that ID. if the id has one or more dates >2010 then include in select. Do I need to put in a sub query?

Upvotes: 0

Views: 56

Answers (2)

paparazzo
paparazzo

Reputation: 45096

SELECT *
FROM Test t
WHERE EXISTS ( SELECT 1
               FROM Test
               WHERE ID = t.ID 
               AND Date >= '01/01/2010'
             )

Upvotes: 2

Eric
Eric

Reputation: 3257

Try the query below. You have to exclude any IDs that have record before '01/01/2010'

SELECT *
FROM Test t
WHERE NOT EXISTS (
    SELECT 1
    FROM Test
    WHERE ID = t.ID AND Date < '01/01/2010'
)

Upvotes: 0

Related Questions