Query that counts the comments for each panelist

I have four tables with the following structure:

1. panelists (panelist_id, first_name, last_name, etc.)
2. projects (project_id, title, created_date, etc.)
3. panelists_on_pro开发者_C百科jects (pp_id, panelist_id, project_id, etc.)
4. comments (comment_id, panelist_id, project_id, etc.)

Imagine I have four panelists on the same project ("x"), but only 3 of the panelists have left a comment. I am trying to figure out a query that counts the comments for each panelist and returns a zero for the panelist attached to project x, but who has not left a comment.

I tried using the following:

SELECT first_name, last_name, COUNT(comment_id)
from panelists
INNER JOIN comments USING (panelist_id)
WHERE project = x

But I only get results for the 3 panelists who have actually left a comment. Any suggestions?

The outer join is the key part, also I think you forgot about the project in the comments join. When you count by a specific column, null values will not be counted.

select
    pj.project_id,
    p.panelist_id,
    p.firstname,
    p.lastname,
    totalcomments = count(c.comment_id)
from
    project pj
    inner join panelists_on_projects pop on pop.project_id = pj.project_id
    inner join panelist p on p.panelist_id = pop.panelist_id
    left outer join comments c on
        c.panelist_id = p.panelist_id
        and c.project_id = pj.project_id
where
    pj.title = 'X'
group by
    pj.project_id,
    p.panelist_id,
    p.firstname,
    p.lastname

Here is my implementation. Although if you know the project_id you can go straight to the panelists_on_projects table.

select
    pan.first_name
    , pan.last_name
    , count(com.comment_id)
from
    projects proj
    inner join panelists_on_project pop
        on proj.panelist_id = pop.panelist_id
    inner join panelist pan
        on pop.panelist_id = pan.panelist_id
    left outer join comments com
        on pan.panelist_id = com.panelist_id
        and com.project_id = proj.project_id
where
    proj.title = 'x'
group by
    pan.first_name
    , pan.last_name

Without the 2nd condition and com.project_id = proj.project_id on the comments join this would be the count of the total number of comments for all projects of the panelists that were on project 'x'

SELECT P.first_name, P.last_name, COUNT(C.comment_id)

FROM panelists as P

LEFT JOIN panelists_on_projects AS PP ON PP.panelist_id = P.panelist_id

LEFT JOIN projects AS PR PN PP.project_id = PR.project_id

LEFT OUTER JOIN comments As C ON C.panelist_id = P.panelist_id AND C.project_id = PR.project_id

WHERE PR.title = "x"