InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 07:31:55 GMT20http://www.sqlservercentral.com/Forums/Topic489765-149-1.aspxThanks 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.regardsSaravanakumar.RFri, 25 Apr 2008 03:31:54 GMTSaravanakumar.Rathinamhttp://www.sqlservercentral.com/Forums/Topic489765-149-1.aspxselect * from table1 where id not in (select id from table1 where status='rework')Fri, 25 Apr 2008 03:11:00 GMTsameyullahhttp://www.sqlservercentral.com/Forums/Topic489765-149-1.aspxHere 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 NULLSELECT T1.*FROM myTable T1WHERE 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.Thu, 24 Apr 2008 08:37:08 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic489765-149-1.aspxThanks 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 & regardsSaravanakumar.RThu, 24 Apr 2008 04:58:12 GMTSaravanakumar.Rathinamhttp://www.sqlservercentral.com/Forums/Topic489765-149-1.aspxselect * from myTable where BatchID not in (select BatchID from myTable where Status = 'Rework')Thu, 24 Apr 2008 04:26:41 GMTRyanRandallhttp://www.sqlservercentral.com/Forums/Topic489765-149-1.aspxHi all,I need a basic query based on the below conditions Sample recordsID BatchID Status1 121 COMPLETE2 121 PENDING3 121 REWORK4 122 ACK5 123 TBKReqd Output ID BatchID Status4 122 ACK5 123 TBKIf the Status = 'REWORK", I don't want the Particular batchID to the output. Kindly help me to write the query.thanks & regardsSaravanakumar.RThu, 24 Apr 2008 04:10:09 GMTSaravanakumar.Rathinam