CODEWITHSUNDEEP
sqloracle-database
Jim Vercoelen
Jim Vercoelen

Reputation: 1077

SQL order by highest amount values other tables

I have this tables

QUESTION

ID  |  Name      
1    question1      
2    question1      
3    question1

ANSWER

ID  |  question_id      
1      1      
2      1      
3      2

COMMENT

ID  |  question_id | answer_id    
1      NULL            1      
2      1               NULL
3      2               NULL

a question can have multiple answers and comments,

a answer belongs to 1 single question,

and a comment belong to 1 single answer OR question (like in Stackoverflow it selfe).

I want to order the questions on highest amount answers and comments.

I'am still a newbe on sql statements, made some query's which doesn't make sence anymore! I would like to post them to but I think its a bit inrelevant because they are wrong as could be! If you want me to post them, Ill show some 'work'.

Thanks

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, you just want joins and aggregation:

select q.*, a.numanswers, c.numcomments
from questions q left join
     (select question_id, count(*) as numanswers
      from answers a
      group by question_id
     ) a
     on q.id = a.question_id left join
     (select question_id, count(*) as numcomments
      from comments c
      where question_id is not null
      group by question_id
     ) c
     on q.id = c.question_id
order by (numanswers + numcoments) desc

This gets the count of comments on the question (not on the associated answers), but that seems to be what you are asking for.

Upvotes: 1

Related Questions