asked on

SQL Query to find row appearing one day, but not other

Hello Experts,

lets say I had the following table. What query would tell me all Names that appear on 1/1/2008, but not on 1/2/2008 (in this case, Jane). I am using SQL Server 2005

Date Name
1/1/2008 John
1/1/2008 Jane
1/2/2009 John
...

ASKER CERTIFIED SOLUTION

brad2575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Louis LIETAER

hello,

this one may help to, it will select all name that are unique

select flag,nname,ndate from (select count(name) as flag,name as nprod, date-app as ndate from test group by name) as myquery where flag = 1

you coud add easily at the end a selection on ndate if needed

sfun28

ASKER

Unfortunately I don't have IDs...just the table as shown.

could you provide the full syntax for this query?

louislietaer - i couldn't quite understand how I would use what you suggested. Could you provide that in the full query?

Louis LIETAER

sorry they was an error (nprod was for my tests)

You table name is 'test'
fields are 'name' and 'date-app'

select flag,nname,ndate from (select count(name) as flag,name as nname, date-app as ndate from test group by name) as myquery where flag = 1

if you want a date selection

select flag,nname,ndate from (select count(name) as flag,name as nname, date-app as ndate from test group by name) as myquery where flag = 1 and ndate='01/01/2008'

sfun28

ASKER

this variation on brad's solution works

Select [Name]
From TableName
where Date = '1/1/2008'
AND [Name] NOT IN (

Select [Name]
From TableName
where Date = '1/2/2008'

)

Louis LIETAER

Mine work to ;)