Skip to content
Advertisement

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

Table Inventory:

Desired result for my query:

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:

The query uses CROSS JOIN to get all possible combinations between locations and products.

Demo here

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement