MySQL query with multiple joins but only join one table at a time-CodePudding

Now I have 3 tables, user, question and answer. And want to query the user's question and answer in one query. I have tried left join but the result was not what I expected. Here is the SQL:

SELECT user.id, question.id as question, answer.id as answer
FROM user
LEFT JOIN question ON user.id = question.user_id
LEFT JOIN answer ON user.id = answer.user_id
where user.id = 1;

And the result comes out like this:

 ------ ---------- -------- 
| id   | question | answer |
 ------ ---------- -------- 
| 1    | 99       | 10     |
 ------ ---------- -------- 
| 1    | 99       | 11     |
 ------ ---------- -------- 
| 1    | 99       | 12     |
 ------ ---------- -------- 
| 1    | 100      | 10     |
 ------ ---------- -------- 
| 1    | 100      | 11     |
 ------ ---------- -------- 
| 1    | 100      | 12     |
 ------ ---------- --------

It would greatly affect the performance if user has lots of questions and answers.
What I expect is like this:

 ------ ---------- -------- 
| id   | question | answer |
 ------ ---------- -------- 
| 1    | 99       | NULL   |
 ------ ---------- -------- 
| 1    | 100      | NULL   |
 ------ ---------- -------- 
| 1    | NULL     | 10     |
 ------ ---------- -------- 
| 1    | NULL     | 11     |
 ------ ---------- -------- 
| 1    | NULL     | 12     |
 ------ ---------- --------

Which means user 1 has 2 questions and 3 answers.
How can I get this result in one query? Or need to seperate with 2 queries, one join with question and one join with answer?

Thanks so much!!

CodePudding user response：

You could just union the result of those 2 tables

SELECT q.user_id as id, q.id AS question, NULL AS answer FROM question q WHERE q.user_id = 1
UNION ALL 
SELECT a.user_id, NULL, a.id FROM answer a WHERE a.user_id = 1

CodePudding user response：

SELECT user.id, question.id as question, answer.id as answer
FROM user
FULL OUTER JOIN question ON user.id = question.user_id
FULL OUTER JOIN answer ON user.id = answer.user_id
where user.id = 1;

I think what you require is a full outer join.