mcmuney Posted March 30, 2012 Share Posted March 30, 2012 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 Quote Link to comment Share on other sites More sharing options...
DavidAM Posted March 30, 2012 Share Posted March 30, 2012 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 Quote Link to comment Share on other sites More sharing options...
Jessica Posted March 30, 2012 Share Posted March 30, 2012 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.