? URGENT: Alternative to full outer join

Get Help
#1

How can I possibly preserve entries from two tables??
Example Structure: Month, Material, Ordered Quantity

Table A (e.g. from Branch 1)
January, Cotton, 10
April, Wood, 5
November, Steel, 12

Table B (e.g. from Branch 2)
February, Plastic, 4
April, Wood, 6
December, Steel, 10

Desired Output:
January, Cotton, 10
February, Plastic, 4
April, Wood, 11
November, Steel, 12
December, Steel, 10

-> I cannot use either LEFT JOIN, nor RIGHT JOIN, nor INNER JOIN, though this is a case that is soooo common and I can't find a way to do it??

#2

Hey, you can use the SQL UNION ALL clause to put all the rows in one table, and then do an aggregation to sum the quantities.

It will depend on your SQL dialect, but something like this should work:

WITH temp AS (
SELECT 
month,
material, 
qty
FROM table_a

UNION ALL 

SELECT
month,
material, 
qty
FROM table_b
)

SELECT 
month, 
material, 
SUM(qty)
FROM temp
GROUP BY month, material

Let me know if this helps!

3 Likes
#3

Oh wow, thank you so much, this solved my problem!! :heart_eyes:
I will def be using this a lot.

1 Like