Hi,
I'm trying to compare 2 sets of data from the same table.
for example
status Recd# Date
COM 1000 08/20/2008
COM 1001 08/20/2008
AVL 1002 08/20/2008
AVL 1000 08/20/2008
AVL 1001 08/20/2008
The result i'm looking for is records with AVL status that are not in COM status. in this case
AVL 1002 08/20/2008
I tried to do subqueries but it did not work at all
something like
select recd# from table
where date = today and status = "AVL" and recd# NOT IN (Select recd# from table where Status= "COM" and date = today)
but return 0 records...
I'll appreciate any help.
thanks!
where
I'm trying to compare 2 sets of data from the same table.
for example
status Recd# Date
COM 1000 08/20/2008
COM 1001 08/20/2008
AVL 1002 08/20/2008
AVL 1000 08/20/2008
AVL 1001 08/20/2008
The result i'm looking for is records with AVL status that are not in COM status. in this case
AVL 1002 08/20/2008
I tried to do subqueries but it did not work at all
something like
select recd# from table
where date = today and status = "AVL" and recd# NOT IN (Select recd# from table where Status= "COM" and date = today)
but return 0 records...
I'll appreciate any help.
thanks!
where