How to do multiple joins on the same table with the same source with same coulmn?

secureselection 0
Viewed 35

Having trouble visualizing how to approach this problem I have. I would like to join the same colmn from the same source multiple times to the same table.

I need a way to create a new coulmn with a filter applied per product type.

The issue is that the table being joined contains the data as such: The item_numbers are repeated per product_type so its all stacked into one column and needs to be seperated.

item_number product_type quantity
1 A 2
2 A 3
2 B 1
3 B 2
4 A 6
5 A 5
5 B 7

This was my attempt which didn't work. I'm pretty new so any help would be greatly appreciated!

SELECT 

tableA.item_number,
tableB.item_number,
tableB.product_type,
tableB.quantity AS productA_qty,
tableB.quantity AS productB_qty

FROM tableA

LEFT OUTER JOIN tableB
ON tableA.item_number = tableB.item_number WHERE product_type = 'A'

LEFT OUTER JOIN bom
ON tableA.item_number = tableB.item_number WHERE product_type = 'B'
sql postgresql
1 Answers

You should use SUM aggregation per item_number and separate the columns by product_type:

SELECT
    item_number,
    sum(case when product_type = 'A' then quantity else 0 end) as product_a_quantity,
    sum(case when product_type = 'B' then quantity else 0 end) as product_b_quantity
FROM table
GROUP BY 1
Rodrigo Cava0
Related