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