mysql database problem in fetching data from 4 tables?

Question

SELECT
    `tbl_users`.`email`,
    `tbl_questions`.`q_id`,
    `tbl_questions`.`question`,
    `tbl_questions`.`tags`,
    `tbl_questions`.`posted_at`,
    SUM(tbl_questions_votes.upvote) AS upvotes,
    SUM(tbl_questions_votes.downvote) AS downvotes
FROM 
    `tbl_users`
INNER JOIN 
    `tbl_answares` 
ON 
    `tbl_answares`.`user_id` = `tbl_users`.`user_id`
LEFT JOIN 
    `tbl_questions` 
ON 
    `tbl_questions`.`q_id` = `tbl_answares`.`q_id`
LEFT JOIN 
    `tbl_questions_votes` 
ON 
    `tbl_questions`.`q_id` = `tbl_questions_votes`.`q_id`
WHERE
    `tbl_users`.`user_status` = 1 AND `tbl_answares`.`user_id` = '6'
GROUP BY
    `tbl_questions`.`q_id`
ORDER BY
    `tbl_questions`.`posted_at`
DESC

Above query is returning email of user that answered the question (another columns are right output) but, i want the email of the user that asked the question.

output: output

Expected Output

`tbl_users`.`email`, // email of user that asked the question
`tbl_questions`.`q_id`,
`tbl_questions`.`question`,
`tbl_questions`.`tags`,
`tbl_questions`.`posted_at`,
SUM(tbl_questions_votes.upvote) AS upvotes,
SUM(tbl_questions_votes.downvote) AS downvotes

Lucas Wieloch · Accepted Answer · 2018-09-06T12:31:29.633

1

Try changing the INNER JOIN on tbl_questions_votes to a LEFT JOIN, since I'd assume it doesn't matter if a question has votes or not.

Secondly, you're joining the table answares and questions both on user_id. I don't think this makes too much sense, it seems to me that you're fetching users with the questions that they did and all the answares they submmited; while the expected output is the questions that this user has answared!

So, I think it would make more sense if you joined like this:

INNER JOIN 
    tbl_answares 
ON 
    tbl_answares.user_id = tbl_users.user_id
INNER JOIN 
    tbl_questions 
ON 
    tbl_questions.q_id = tbl_answares.q_id
LEFT JOIN 
    tbl_questions_votes 
ON 
    tbl_questions.q_id = tbl_questions_votes.q_id
LEFT JOIN 
    tbl_users usrs2 
ON 
    tbl_questions.user_id = usrs2.user_id

This joins a user with his answares along with the answares particular questions and the votes casted for those questions, without excluding questions that has no votes; and finnally joining with the users that made each question you are retrieving.

If you have any doubts around joining tables in SQL, I recommend this great SO topic and this other one

edited Sep 06 '18 at 12:31

answered Sep 05 '18 at 12:25

Lucas Wieloch

818
7
19

i get the email of the user that answered the question but i want the email of the user that asked the question. after implementing your solution i got result perfectly but only the email column is not right(wrong email). – sandip Sep 05 '18 at 13:01
On your select you have `tbl_users`.`email`. This selects the user that answered the question because there is an `INNER JOIN` on `answares`, and a `WHERE` statement restricting only certain `user_id` on `answares`. This is all correct! The thing is, if you want to select the `email` of the user that asked the question, you have to fetch it from `tbl_questions.user_id` – Lucas Wieloch Sep 05 '18 at 13:22
it is possible with nested query ? – sandip Sep 05 '18 at 14:03
It is possible with a subquery indeed. There are many ways you can retrieve this data, but, the **best** way depends on a lot of things that are specific of your application. – Lucas Wieloch Sep 05 '18 at 15:16
@sandip came back with a tip: Try adding a self join at the end like this -> `LEFT JOIN tbl_users usrs2 ON tbl_questions.user_id = usrs2.user_id`, and then use `usrs2.email` to fetch the email of the user that asked the question. I didn't test this, not sure if it'll work. Might update answer later with this – Lucas Wieloch Sep 05 '18 at 16:13
thanks Lucas Wieloch. Your suggestions are work perfectly. – sandip Sep 06 '18 at 11:00

mysql database problem in fetching data from 4 tables?

1 Answers1