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