CODEWITHSUNDEEP
phpmysqlsqldatabase
dshukertjr
dshukertjr

Reputation: 18573

joining three tables and counting values

I have three tables like the following:

user             post                          answer

user_id          post_id    user_id            answer_id     post_id
1                1           2                  1            4
2                2           3                  2            2
3                3           2                  3            2
4                4           1                  4            1
                                                5            3
                                                6            2

So, I have three tables user, post, and answer. In the user table, I have the information of my users. In the actual table, there are other columns like usernames and stuff, but I have omitted them because they are not related to the question.

In the post table, I have the information of stuff that the user posted on my webpage. post id is just id numbers for each post, and user_id indicates which user posted the post.

In the answer table, I have the information of answers that were posted on each post. answer_id is just id numbers on each answers, and post_id indicates which post the answer was posted on.

What I want to do with these three tables is I want to select user_id in the order of most answers to least answers.

In this case, user_id 1 posted one post (post_id 4) and that post_id 4 got one answer (answer_id 1), user_id 2 posted two posts (post_id 1, and 3) and those posts got two answers (answer_id 4 and 5), user_id 3 posted one post(post_id 2) and got three answers (answer_id 2, 3, 6), and user_id 4 posted no posts, so got 0 answers, which will give me user_id in the order of 3, 2, 1, 4.

I hope my question make sense. Sorry for a complicated question. Please let me know if you have any advise to make my tables more organized. I am new to DB, so my table structure might not be in the ideal fashion.

Upvotes: 1

Views: 73

Answers (2)

mazedlx
mazedlx

Reputation: 1455

select u.user_id, count(a.answer_id) as cnt_answers from user u
left join post p on p.user_id = u.user_id
left join answer a on a.post_id = p.post_id
group by u.user_id
order by cnt_answers desc;

this should give the needed results.

Upvotes: 2

mikeyq6
mikeyq6

Reputation: 1148

Try this:

select u.user_id, count(a.answer_id) as answerCount
from user u
inner join post p on p.user_id = u.user_id
inner join answer a on a.post_id = p.post_id
group by u.user_id
order by count(a.answer_id) desc;

Upvotes: 3

Related Questions