SQL join between two tables to get all the results-CodePudding

I am trying to write a simple query but I have no luck getting the right output

I have two tables

[Stores]

StoreID StoreName

[Inventory]

ItemID StoreID Quantity

Store has all the stores (1-5) and Inventory has all the items (1-20000) with quantities currently located in the stores.

If I do the outer join i get

StoreID ItemID  Quantity
1       1       5
2       1       4
1       2       1
1       5       0

What I am trying to get is list of all the stores with all the items no mater if the Quanty is null. For example

StoreID ItemID  Quantity
1       1       5
2       1       4
3       1       NULL
4       1       NULL
5       1       NULL
1       2       1
2       2       NULL
3       2       NULL
4       2       NULL
5       2       NULL

How can I accomplish this ? Thanks in advance

Here is what I tried

Objekti=Stores, Artikli=Products, SubQuery=Inventory - just translation

But I get duplicated results

Here is the query from above

SELECT
  Objekti.ObjekatID
 ,Objekti.ObjekatNaziv
 ,Artikli.SifraKase
 ,SubQuery.Slobodno
FROM dbo.Objekti
CROSS JOIN dbo.Artikli
INNER JOIN (SELECT
    Artikli.SifraKase
   ,Stanje2_PoObjektu.Slobodno
   ,Stanje2_PoObjektu.ObjekatID
  FROM dbo.Artikli
  LEFT OUTER JOIN dbo.Stanje2_PoObjektu
    ON Artikli.SifraKase = Stanje2_PoObjektu.SifraKase) SubQuery
  ON Artikli.SifraKase = SubQuery.SifraKase

CodePudding user response：

I'm not sure why you need the product table for this query. I assume there is information in it that could be needed for other queries but based on the result set, this one only needs the ItemID. That can be pulled from the inventory table.

SELECT 
      Stores.StoreID
    , Stores.StoreName
    , Inventory.ItemID
    , Inventory.Quantity
 FROM Stores
 LEFT JOIN Inventory ON Stores.StoreID = Inventory.StoreID
 ORDER BY Inventory.ItemID ASC, Stores.StoreID ASC

If product is needed, I would left join it to Inventory.

SELECT 
      Stores.StoreID
    , Stores.StoreName
    , Inventory.ItemID
    , Product.ProductName
    , Inventory.Quantity
 FROM Stores
 LEFT JOIN Inventory ON Stores.StoreID = Inventory.StoreID
 LEFT JOIN Product ON Product.ItemID = Inventory.Item.ID
 ORDER BY Inventory.ItemID ASC, Stores.StoreID ASC

This approach could return a lot of nulls if your tables are missing data but it should take care of your duplicate issue.

CodePudding user response：

SELECT 
user_register.userid,user_register.emailid,user_register.password,user_register.createdon,user_register.updatedon,user_details.firstname,user_details.lastname,user_details.mobileno,user_details.countryid,master_country.countryname,master_state.statename,user_details.stateid,user_details.city,user_details.usertypeid,master_usertype.usertype,user_details.profileimg from user_register join user_details join master_country join master_state join master_usertype on user_register.userid=user_details.userid and master_country.countryid=user_details.countryid and user_details.stateid=master_state.stateid and user_details.usertypeid=master_usertype.usertypeid where user_register.userid=?