How to make the right join for specific results

[mcmuney]

What would be the correct join between Table1 and Table2 to get the Expected Results?

 

Table1

x y

A 2

B 4

D 6

 

Table2

x z

A 1

C 3

E 5

 

Expected Results

x y z

A 2 1

B 4 0

C 3 0

D 6 0

E 0 5

[DavidAM]

I don't think you can do it in a single query with any kind of JOIN. But you can do it with a UNION using two LEFT JOINs

 

-- All Rows in Table 1 -- 
SELECT TABLE1.x, TABLE1.y, IFNULL(TABLE2.z, 0) AS z
FROM TABLE1 LEFT JOIN TABLE2 USING TABLE1.x = TABLE2.x

UNION

-- All Rows in Table 2 with no matching X in Table 1
SELECT TABLE2.x, 0, TABLE2.z
FROM TABLE2 LEFT JOIN TABLE1 ON TABLE2.x = TABLE1.x AND TABLE1.y IS NULL

ORDER BY 1

[Jessica]

I believe you could do this with an outer join, and a case statement. I've been working on a project in MSSQL for 2 years so I had to look up MySQL's syntax for it but I think this is correct, didn't try it.

SELECT t1.x, 
CASE t1.y
   WHEN NULL THEN 0
   ELSE t1.y
END CASE, 
CASE t2.z
   WHEN NULL THEN 0
   ELSE t2.z
END CASE
FROM t1 
OUTER JOIN t2 ON t1.x = t2.x