CODEWITHSUNDEEP
mysqljoin
Lux Interior
Lux Interior

Reputation: 321

Mysql multiple joins and count

I'm trying to create an SQL statement that joins two tables and returns a count. I need all the records returning even if there are no items found for the count.

If I just do this:

SELECT s.subject,s2.subject from subjects s 
left join subjects s2 on s.parent_id = s2.id

It's fine but there's no count. I tried joining it to the other table I need to do the count on by doing this:

SELECT s.subject,s2.subject,count(q.id) from subjects s 
left join subjects s2 on s.parent_id = s2.id 
join questions q on q.subject_id = s.id

but I just get one result that doesn't have any meaning at all.

Upvotes: 2

Views: 112

Answers (2)

GavinCattell
GavinCattell

Reputation: 3963

If you can have zero questions, you'll need to add the COUNT to a subquery, then you can LEFT JOIN on the results like this:

SELECT s.subject, s2.subject, NVL(q.q_count,0) as question_count 
FROM subjects s 
LEFT JOIN subjects s2 ON s.parent_id = s2.id 
LEFT JOIN (SELECT q.subject_id, count(1) as q_count 
  FROM questions q
  GROUP BY q.subject_id) q ON q.subject_id = s.id

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Try this:

 SELECT s.subject, s2.subject, COUNT(q.id) 
 FROM subjects s 
 LEFT JOIN subjects s2 ON s.parent_id  = s2.id 
 LEFT JOIN questions q ON q.subject_id = s.id 
 GROUP BY s.subject_id, s2.subject_id, q.subject_id

Upvotes: 1

Related Questions