Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

same table query... 1

Status
Not open for further replies.

cesaru

Technical User
Jan 31, 2008
19
US
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
 
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
 
Thanks! it worked like a charm...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top