CODEWITHSUNDEEP
phpmysqlsql
aryan
aryan

Reputation: 25

How to join these tables?

I have three tables:

users
-----------------------------------
id | firstname | lastname | username
-----------------------------------
1  | John     | Doe      | jdoe
-----------------------------------
2  | Maria    | T.       | marty
-----------------------------------
3  | Alex     | White    | alexw
-----------------------------------

questions
--------------------------------------------
id | user_id | title
--------------------------------------------
1  | 2       | My first question?
--------------------------------------------
2  | 3       | One more question?
--------------------------------------------
3  | 3       | The third question?
--------------------------------------------

answers
----------------------------------------------
id | question_id | description
----------------------------------------------
1  | 2           | Answers to the 2nd question
----------------------------------------------
2  | 1           | Answer for 1st question
----------------------------------------------
3  | 1           | Another answer for 1st
----------------------------------------------

Now,I want to retrieve all question with user's(asker's) firstname, lastname, username and count of total answers given for a question.

Please help writing it. Since I have messed my query, not posted here.

Thanks

Upvotes: 0

Views: 79

Answers (2)

xdazz
xdazz

Reputation: 160873

SELECT q.*, u.*, COUNT(a.id) as answer_count
FROM questions q 
LEFT JOIN users u ON q.user_id = u.id
LEFT JOIN answers a ON a.question_id = q.id
GROUP BY q.id

Upvotes: 3

Siva Charan
Siva Charan

Reputation: 18064

select users.fistname,users.lastname,users.username 
from users, questions, answers
where users.id = questions.user_id and questions.id = answers.question_id

Upvotes: -1

Related Questions