mysql - SQL 语句显示来自 3 个表的数据

Question

我想在一个查询中显示 3 个表，这样构造这些表

1. tbl_user
+-----------+------
   userId   | name 
+-----------+------
 uid1       | jorge
 uid2       | kaka
 uid3       | victor
 uid4       | james

2. tbl_question
+-----------+--------+------------------------------------+--------------
 questionId | userId | question                           | answer_count
+-----------+--------+------------------------------------+--------------
 qid1       | uid2   | what do you think about elephant?  | 3
 qid2       | uid2   | what do you think about cat?       | 1
 qid3       | uid2   | what do you think about shark ?    | 0
 qid4       | udi3   | what do you think about snake ?    | 1

3. tbl_answer
+-----------+--------+------------------------------------+--------------
 questionId | userId | answer
+-----------+--------+------------------------------------+--------------
 qid1       | uid4   | it's have a big body
 qid1       | uid2   | it's have a long nose
 qid1       | uid1   | it's have 4feet
 qid2       | uid3   | it's a cute animal
 qid4       | uid2   | it's have a poison

我想显示所有具有指定userid的表的数据，只要我使用下面的SQL语句

select tbl_user.userId,name,question,answer_count,tbl_answer.userId,answer from tbl_question INNER JOIN tbl_user ON tbl_question.userId=tbl_user.userId LEFT JOIN tbl_answer ON tbl_answer.questionId=tbl_question.questionId WHERE tb_question.userId='uid2';

结果如下:

userId | name   | questionId | question                             | answer_count | tbl_answer.userId | answer 
-------+--------+---------------------------------------------------+--------------+-------------------+---------------------
 uid2  | kaka   |    qid1    | what do you think about elephant ?   |  3           |      uid4         | it's have a big body
 uid2  | kaka   |    qid1    | what do you think about elephant ?   |  3           |      uid2         | it's have a long nose
 uid2  | kaka   |    qid1    | what do you think about elephant ?   |  3           |      uid1         | it's have 4feet
 uid2  | kaka   |    qid2    | what do you think about cat   ?      |  1           |      uid3         | it's a cute animal
 uid2  | kaka   |    qid3    | what do you think about shark ?      |  0           |      null         | null

上面的结果出现了userid = 'uid2'的所有数据，但是我想要结果:

tbl_answer.userId='uid2'如果tbl_answer.userId是null或 tbl_answer.userId<>'uid2'如下所示:

userId | name   | questionId | question                             | answer_count | tbl_answer.userId | answer 
-------+--------+--------------------------------------+--------------+-------------------+---------------------
 uid2  | kaka   |    qid1    | what do you think about elephant ?   |  3           |      uid2         | it's have a long nose
 uid2  | kaka   |    qid2    | what do you think about cat   ?      |  1           |      uid3         | it's a cute animal
 uid2  | kaka   |    qid3    | what do you think about shark ?      |  0           |      null         | null

如何实现？

Answer 1

添加到WHERE子句:

WHERE <...> AND (tbl_answer.userId = 'uid2' OR tbl_answer.userId IS NULL)