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
...
LVL 1
sfun28Asked:
Who is Participating?
 
brad2575Connect With a Mentor Commented:
Assumign you have an ID field somewhere in this table and you know the dates ahead of time it would be this

Select *
From TableName
where Date = '1/1/2008'
AND TableID NOT IN (

  Select TableID  
  From TableName
  where Date = '1/2/2008'

)
0
 
louislietaerCommented:
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
0
 
sfun28Author Commented:
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?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
louislietaerCommented:
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'


0
 
sfun28Author Commented:
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'

)
0
 
louislietaerCommented:
Mine work to ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.