March 9, 2023 at 8:14 am
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
March 9, 2023 at 9:12 am
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;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy