CODEWITHSUNDEEP
mysqlsqldatabase
ltvie
ltvie

Reputation: 973

SQL statement to show data from 3 tables

I have 3 tables that I want to show in one query, This structure these tables

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

I want to display data from all table with specified userid, so far as I use SQL statement below

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';

here is the result:

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

above results appear all the data with userid = 'uid2', but that I want results:

tbl_answer.userId='uid2' and if tbl_answer.userId is null or tbl_answer.userId<>'uid2' like below: 

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

How to achieve that?

Upvotes: 1

Views: 63

Answers (2)

rtruszk
rtruszk

Reputation: 3922

This is a query which solves your problem:

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 tbl_question.userId='uid2'
AND (tbl_answer.userId = 'uid2' 
     OR 
     tbl_answer.userId IS NULL 
     OR     
     (select count(questionId) from tbl_answer a where a.questionId=tbl_question.questionId and a.userId = 'uid2') = 0
     AND
     tbl_answer.userId = (select min(userId) from tbl_answer a where a.questionId=tbl_question.questionId)
)

It returns all the question asked by user2.
If user2 is one of answerers then only his answer is returned.
If there is no answer then null is returned.
If there are only answers provided by other users than user2 then the answer of user with miimal id is returned.

Upvotes: 1

potashin
potashin

Reputation: 44581

Add to the WHERE clause:

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

Upvotes: 3

Related Questions