I have two simple SQL tables and I would to combine them into one table. I would like to combine Table A (actually called view_gradedchallenges) and Table B (actually called view_totalpartpoints) into Table AB.
I read that a FULL OUTER JOIN might do the job, but that is not supported in MySQL for some reason. An alternative is a LEFT JOIN, RIGHT JOIN and then combine the two queries using UNION or UNION ALL. But that gives me the following result:
Can anybody help me to find the query that will generate Table AB?
This is the query I am currently using:
SELECT * from view_gradedchallenges LEFT OUTER JOIN view_totalpartpoints ON view_gradedchallenges.author = view_totalpartpoints.uid
UNION
SELECT * from view_gradedchallenges RIGHT OUTER JOIN view_totalpartpoints ON view_gradedchallenges.author = view_totalpartpoints.uid
If you would use the names TableA and TableB instead of their original names, this would be the query:
SELECT * from TableA LEFT OUTER JOIN TableB ON TableA.author = TableB.uid
UNION
SELECT * from TableA RIGHT OUTER JOIN TableB ON TableA.author = TableB.uid
But unfortunately, this leads to the wrong result (see the left part of the second image). I want to know how to solve this. Please help me (instead of showing a similar thread that doesn't answer this particular question)