Thanks Sergey!


>Try
>

>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
>>
>>

>>
>>I wrote this query to pull parts that have not been received. Not received is defined as LocationId = 0. You can see in the
>>above data set that part 1 was sent from warehouse 1 to warehouse 2. At warehouse 2 it was put on location 6.
>>
>>When I call this query again, I get back the second record. I should not get any records back from this data set because the
>>newest record has it on a location, so therefore it's received.
>>
>>Here's my query:
>>
>>

>>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
>>
>>

>>
>>What do I need to do to adjust it so it meets the requirements?