Search code examples
sqlsql-serverjoin

Questions on Joining

TransactionList (id is not unique) e.g., Transaction 1 has item 1 (apple) with quantity = 1

id item quantity
1 1 1
2 1 1
2 2 1
2 3 1
3 1 1
3 2 1

ItemList

id name
1 Apple
2 Banana
3 Orange

How can I join the above two tables and get the following expected result? Expected Result (include all possible items in all transaction and the corresponding value):

id item quantity Name
1 1 1 Apple
1 2 0 Banana
1 3 0 Orange
2 1 1 Apple
2 2 1 Banana
2 3 1 Orange
3 1 1 Apple
3 2 1 Banana
3 3 0 Orange

I cannot get my head around on this question. Not sure if cross join is useful in my case.

Solution

  • One method is with a CROSS JOIN and conditional aggration:

    SELECT 
     t.id
    ,i.id
    ,SUM(CASE WHEN t.item = i.id THEN t.quantity ELSE 0 END) AS quantity
    ,i.name
FROM dbo.ItemList AS i
CROSS JOIN dbo.TransactionList AS t
GROUP BY 
     t.id
    ,i.id
    ,i.name
ORDER BY 
     t.id
    ,i.id;