Query Scenario

[RadioButton]

Hello, I need some help with a SQL query. Example table: Table myTable Columns myId, myDate If I have rows in the table with the same id like this: myid mydate ---- -------- 1 01/01/1999 1 blank 1 01/01/1999 2 01/02/1999 I would like to filter out the id completely if I find the id has a record with a date and a record with a blank date. If the filter works my results should be: myid mydate ---- -------- 2 01/02/1999 Id 2 remains because it does not have records with a blank date. Thank you so much for the help.

[Michael Potter]

Your request is somewhat confusing but, I think I grasped the basic concept. I assume you are using blank as a term that represents NULL.

SELECT 
    myid, 
    mydate
FROM 
    myTable
WHERE 
    myid NOT IN (SELECT 
                     myid
                 FROM 
                     myTable
                 WHERE 
                     mydate IS NULL)

[Ashfield]

Try SELECT myid, mydate FROM myTable mt1 WHERE not exists (select 1 from myTable mt2 where mt2.myID = mt1.myid and mt2.mydate is null) Assuming the table is properly indexed this should execute quicker as a not exists is quicker than a not in.

Bob Ashfield Consultants Ltd