April 24, 2008 at 4:10 am
Hi all,
I need a basic query based on the below conditions
Sample records
ID BatchID Status
1 121 COMPLETE
2 121 PENDING
3 121 REWORK
4 122 ACK
5 123 TBK
Reqd Output
ID BatchID Status
4 122 ACK
5 123 TBK
If the Status = 'REWORK", I don't want the Particular batchID to the output.
Kindly help me to write the query.
thanks & regards
Saravanakumar.R
April 24, 2008 at 4:26 am
select * from myTable where BatchID not in (select BatchID from myTable where Status = 'Rework')
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 24, 2008 at 4:58 am
Thanks for your reply,
I did the same, but the performance of the query is not really good, it taking such a long time to get the result, and every row the query touches twice.
is it possible to get same thing with better result using CTE / derived queries ?
thanks & regards
Saravanakumar.R
April 24, 2008 at 8:37 am
Here are two more options:
[font="Courier New"]SELECT DISTINCT
T1.*
FROM
myTable T1
LEFT JOIN myTable T2 ON T1.BatchID = T2.BatchID
AND T2.Status = 'Rework'
WHERE
T2.BatchID IS NULL
SELECT
T1.*
FROM
myTable T1
WHERE
NOT EXISTS (SELECT * FROM myTable T2 WHERE T2.Status = 'Rework' AND T2.BatchID = T1.BatchID)[/font]
The first should outperform the second if your indexing is correct.
April 25, 2008 at 3:11 am
select * from table1 where id not in (select id from table1 where status='rework')
April 25, 2008 at 3:31 am
Thanks friend,
The mentioned query does not meet the requirement.
If we run the query BATCH ID 121 and the ID 1 & 2 are shown which is not required.
regards
Saravanakumar.R
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply