Tom Schwendler Tom Schwendler -4 years ago 126
SQL Question

Trouble Filtering in Subquery or Join Statement

I'm querying an inventory database in SQL Server 2012 to try to get a list of Items that are finished goods. Every Item has a record in an "Items" table. Some of the Items have been grouped into pallets. All items in the same pallet have a common "Pallet Number" When a pallet is shipped, they mark one of the Items in the pallet as shipped. The others are not changed. What I need to do is make a query that correctly finds Items that are not shipped. This query should look for Items that are part of the same pallet. If one Item in the pallet is shipped, than all Items in that pallet should not be included in the returned list.

A query to find all Items that are part of a shipped pallet is:

select p.* from Items i
inner join Items p on i.PalletNum = p.PalletNum
where i.Status = 'SHIPPED'

The main query to search for the current inventory is:

select * from Items
where i.Status = 'FINISHED GOODS'

If I run this query, it will find all the items that a part of a shipped pallet. What I would like to do is remove all of the items returned from the first query out of the second query. I've tried doing this with a subquery but it's not working correctly. Here is my try using a subquery:

select inv.* from Items inv
where inv.Status = 'FINISHED GOODS' and not exists (
select p.* from Items i
inner join Items p on i.PalletNum = p.PalletNum
where i.Status = 'SHIPPED')

This query returns nothing. I've also tried doing this by using a Join statement for the subquery, but that's not working either. Any help is appreciated.

Answer Source

One method is a correlated subquery

select v.*
from Items i
where v.Status = 'FINISHED GOODS' and
      not exists (select 1
                  from Items p 
                       where i.PalletNum = p.PalletNum and
                             p.Status = 'SHIPPED'
                 ) ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download