same table query... 1

[cesaru]

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

 

[PHV]

SELECT Recd#
FROM table
WHERE Date=TODAY AND status IN('AVL','COM')
GROUP BY Recd#
HAVING COUNT(*)=1 AND MIN(status)='AVL'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cesaru]

Thanks! it worked like a charm...