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!

Register Log in
  • Congratulations wOOdy-Soft 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

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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Thanks! it worked like a charm...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

daYorkie
  • Locked
  • Question
How many UNIONs can i use in a single query?
Replies
2
Views
375
daYorkie
daYorkie
CPFB
  • Locked
  • Question
Update Query Asssitance Please
Replies
0
Views
250
CPFB
CPFB
evgeniosc
  • Locked
  • Question
Copy rows as they inserted on a replica table using trigger
Replies
0
Views
292
evgeniosc
evgeniosc
robertgi
  • Locked
  • Question
infomix query
Replies
2
Views
272
PHV
PHV
KaronW
  • Locked
  • Question
Do multiple "case" lines run against each line or is each run against the table ?
Replies
0
Views
273
KaronW
KaronW

Part and Inventory Search

Sponsor

Back
Top