asked
Comparing rows in same column SQL Server
Hi How would I get the result (last table) from the above data sets. Displays all T2 where no barcodes appeared in T6. ![alt text][1] [1]: /storage/temp/4593-aa.jpg
So you want to select items where transcode is T3 and barcode isn't in the list of barcodes where transcode = T6?
There are many ways of getting the desired results. One way is to use a correlated subquery: select a.[Date], a.TransCode, a.BarCode from YourTable a where a.TransCode = 'T3' and not exists ( select 1 from YourTable where TransCode = 'T6' and BarCode = a.BarCode ); go The other way is to use the left join: select a.[Date], a.TransCode, a.BarCode from YourTable a left join YourTable b on a.BarCode = b.BarCode and b.TransCode = 'T6' where a.TransCode = 'T3' and b.TransCode is null; go Which of the 2 queries will perform better is not possible to tell without knowing the details about the data (indexes, record count, cardinalityof values etc., but is easy to check by highlighting both queries and hitting Ctrl + L in SSMS query window to display the estimated plans for both. These should reveal which query is better. Hope this helps. Oleg
18 People are following this question.
