Запрос, который подсчитывает комментарии для каждого участника дискуссии

#sql #join

#sql #Присоединиться

Вопрос:

У меня есть четыре таблицы со следующей структурой:

panelists ( panelist_id , first_name last_name и т.д.)
projects ( project_id , title created_date и т.д.)
panelists_on_projects ( pp_id , panelist_id project_id и т.д.)
comments ( comment_id , panelist_id project_id и т.д.)

Представьте, что у меня есть четыре участника дискуссии в одном проекте («x»), но только 3 участника дискуссии оставили комментарий. Я пытаюсь вычислить запрос, который подсчитывает комментарии для каждого участника дискуссии и возвращает ноль для участника дискуссии, прикрепленного к проекту x, но который не оставил комментарий.

Я попытался использовать следующее:

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

Но я получаю результаты только для 3 участников дискуссии, которые фактически оставили комментарий. Есть предложения?

1. Пожалуйста, всегда указывайте, какую базу данных вы используете. SQL сильно различается между ними; например, SQL Server имеет много возможностей, недоступных в MySQL (и наоборот).

Ответ №1:

outer join Это ключевая часть, также я думаю, что вы забыли о проекте при объединении комментариев. При подсчете по определенному столбцу значения null не будут учитываться.

 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

1. Второе условие для объединения комментариев не обязательно, поскольку вы уже будете ограничены одним проектом уточнением заголовка в предложении where.

2. @tgandrews неверно. Предложение where выбирает только правильную строку в таблице проекта, оно никак не фильтрует комментарии без второго условия объединения. Запрашивающий не хочет, чтобы учитывались комментарии для других проектов.

3. @Aleksi — Да, вы правы. Я изменил ответ, чтобы он был таким же правильным, как ваш.

4. @dotjoe Разве это не должно быть count(c.comment_id) AS totalcomments вместо totalcomments = count(c.comment_id) ?

5. @Aleksi вы можете сделать любой способ в tsql 🙂 Я предпочитаю, = чтобы имена всех столбцов были в строке.

Ответ №2:

Вот моя реализация. Хотя, если вы знаете project_id, вы можете сразу перейти к таблице panelists_on_projects.

 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

Без выполнения 2-го условия and com.project_id = proj.project_id при объединении комментариев это было бы подсчетом общего количества комментариев для всех проектов участников дискуссии, которые были в проекте ‘x’

Ответ №3:

 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"