SELECT p.PartNo, i.SerialNo AS Serial, i.PurchaseOrder, p.Description, u.FullName, w.Caption AS Warehouse, w.WarehouseCode, i.* FROM Inventory i JOIN Parts p ON p.PartId = i.PartId JOIN Warehouses w ON w.WarehouseId = i.WarehouseId JOIN Users u ON u.UserId = i.UserId WHERE i.WarehouseId = @WarehouseId AND i.LocationId = 0 AND NOT EXISTS (SELECT 1 FROM Inventory i2 WHERE i2.WarehouseId = i.WarehouseId AND i2.PartId = i.PartId AND i2.LocationId <> 0) ORDER BY p.PartNo, i.SerialNo>I'm have this Inventory table
>InventoryId PartId WarehouseId LocationId LocationInfo SerialNo Quantity >----------- ------- ----------- ----------- ------------- --------- --------- >2254 1 2 6 Some Info ABC-123 1 >2138 1 2 0 NULL XYZ-456 1 >2137 1 1 1 More Info NULL 1 > >>
>CREATE PROCEDURE ap_GetPartsNotReceived > @WarehouseId INT > >AS >BEGIN > > SELECT p.PartNo, i.SerialNo AS Serial, l.Location, i.LocationInfo, i.PurchaseOrder, > p.Description, u.FullName, > w.Caption AS Warehouse, w.WarehouseCode, i.* > FROM Inventory i > JOIN Parts p ON p.PartId = i.PartId > JOIN Warehouses w ON w.WarehouseId = i.WarehouseId > JOIN Users u ON u.UserId = i.UserId > FULL JOIN Locations l ON l.LocationId = i.LocationId > WHERE i.WarehouseId = @WarehouseId AND > i.LocationId = 0 > ORDER BY p.PartNo, i.SerialNo > >>