SQL Server select parts, fill with 0 no result

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.

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.

Demo here