1

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.

Click here for an image that makes it clearer

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:

Click here for an image that makes it clearer

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)

David
  • 11
  • 2
  • And if that duplicate does not work for you then you should at least share your current query – juergen d Sep 04 '17 at 15:34
  • This is my query: 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 'TableA' and 'TableB' instead of their real 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 this gives the wrong result – David Sep 04 '17 at 15:48
  • Add it in a readable form into your question. – juergen d Sep 04 '17 at 15:49
  • Sorry, added it – David Sep 04 '17 at 15:58
  • try this, I just renamed the author's column SELECT author as uid, total_points, times_graded from TableA LEFT OUTER JOIN TableB ON TableA.author = TableB.uid UNION SELECT uid, total_points, times_graded from TableA RIGHT OUTER JOIN TableB ON TableA.author = TableB.uid – Yosra Hamza Sep 04 '17 at 16:03
  • That's it, thank your very much!! I already tried "author as uid" after the first SELECT, but it didn't work since I also put it behind the second SELECT. Now that I just use "uid" after the second SELECT (as you suggest), it works great! Thank you so much! – David Sep 04 '17 at 16:23

0 Answers0