Multiple tables in MySQL

• Piyushbhatt

  Old Hand

  Points: 374

  More actions

  March 9, 2023 at 8:14 am

  #4158744

  My have three tables (Stores, Item, and StoreInv) and I'm attempting to construct a join that includes all entries from Stores and Items even if no matching records exist in StoreInv. Probably best explained using an example:

  Table for storage:

  StoreId
  -------
  Store1
  Store2
  Store3

  Table of contents:

  ItemId
  -------
  A
  B
  C

  StoreInv table (only records for items in stock at the store):

   

  ItemId StoreId Qty 
  ------- ------- ---
  A Store1 6
  B Store1 2
  B Store2 4

  Desired output:

  StoreId ItemId Qty 
  ------- ------ ---
  Store1 A 6
  Store2 A 0 (or null)
  Store3 A 0 (or null)
  Store1 B 2
  Store2 B 4
  Store3 B 0 (or null)
  Store1 C 0 (or null)
  Store2 C 0 (or null)
  Store3 C 0 (or null)

  What I've attempted thus far:

   

  SELECT str.StoreId, itm.ItemId, inv.Qty
  FROM Item itm
  LEFT JOIN StoreInv inv ON inv.ItemId = itm.ItemId
  RIGHT JOIN Stores str on str.StoreId = inv.StoreId

  Result (not what I expected):

   

  StoreId    ItemId   Qty 
  -------    ------   ---
  Store1     A         6
  Store1     B         2
  Store2     B         4
  Store3     null      null

• Ken McKelvey

  SSCoach

  Points: 18909

  More actions

  March 9, 2023 at 9:12 am

  #4158762

  SELECT S.StoreId, I.ItemId
     ,COALESCE(X.Qty, 0) AS Qty
  FROM Stores S
     CROSS JOIN Item I
     LEFT JOIN StoreInv X
        ON S.StoreId = X.StoreId
           AND I.ItemId = X.ItemId
  ORDER BY ItemId, StoreId;