MySQL - can't get a left outer join with a count(*) to return the items where count = 0
mysql, sql
Solution
This is because the `where problem_id = 400` removes rows with no corresponding `suggested_solution_comments` row. Moving the condition from the `where` filter to the `on` clause should address the problem:
select s.display_order, s.section_name, s.solution_section_id ,count(c.comment_id)
AS comment_count
from solution_sections s
left outer join suggested_solution_comments c
ON (c.solution_part = s.solution_section_id) AND problem_id = 400
group by s.display_order, s.section_name, s.solution_section_id
order by display_order;
Problem
I have this schema: ``` mysql> describe suggested_solution_comments; +-----------------------+----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+----------------+------+-----+---------+----------------+ | comment_id | int(10) | NO | PRI | NULL | auto_increment | | problem_id | int(10) | NO | | NULL | | | suggested_solution_id | int(10) | NO | | NULL | | | commenter_id | int(10) | NO | | NULL | | | comment | varchar(10000) | YES | | NULL | | | solution_part | int(3) | NO | | NULL | | | date | date | NO | | NULL | | | guid | varchar(50) | YES | UNI | NULL | | +-----------------------+----------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql> describe solution_sections; +---------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+---------------+------+-----+---------+----------------+ | solution_section_id | int(10) | NO | PRI | NULL | auto_increment | | display_order | int(10) | NO | | NULL | | | section_name | varchar(1000) | YES | | NULL | | +---------------------+---------------+------+-----+---------+----------------+ ``` My query is this: ``` select s.display_order, s.section_name, s.solution_section_id , count(c.comment_id) AS comment_count FROM solution_sections s left outer join suggested_solution_comments c ON (c.solution_part = s.solution_section_id) where problem_id = 400 group by s.display_order, s.section_name, s.solution_section_id order by display_order; ``` it returns only rows where there is a count > 0 but if the count is 0 it doesnt return those rows. Any idea how to make it return all the rows? :) Thanks!!