I have the following tables
- Location (Id, locationName)
- Inventory (productid, qty, locationid)
With the following data, I need to query to show all locations per productid, even when not in inventory table. example of records below.
Table Location
Id Location Name -------------------- 1 Plant 2 Warehouse 3 Container
Table Inventory
:
Productid Qty Locationid ----------------------------- 45 30 1 45 56 2 3 15 1 3 50 3 15 25 3
Desired result for my query:
Productid Qty LocationName --------------------------------- 45 30 Plant 45 56 Warehouse 45 0 Container 3 15 Plant 3 0 Warehouse 3 50 Container 15 0 Plant 15 0 Warehouse 15 25 Container
So far I have tried many different ways but no luck, so any help will be appreciated.
Advertisement
Answer
You can use the following query:
SELECT p.ProductId, COALESCE(qty,0) AS qty, [Location Name] FROM LOCATION l CROSS JOIN (SELECT DISTINCT ProductId FROM Inventory) AS p LEFT JOIN Inventory i ON l.Id = i.locationid AND p.Productid = i.Productid ORDER BY Productid, [Location Name]
The query uses CROSS JOIN
to get all possible combinations between locations and products.