question

lomas avatar image
lomas 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
sql server 2008 r2
aa.jpg (44.6 KiB)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So you want to select items where transcode is T3 and barcode isn't in the list of barcodes where transcode = T6?
0 Likes 0 ·
Yes absolutely right. Thanks
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Mate. Its perfectly working.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.