sql - SQL issue with RIGHT JOINS

Question

The following SQL query does exactly what it should do, but I don't know how to change it so it does what I need it to do.

SELECT 
    a.id AS comment_id, a.parent_comment_id, a.user_id, a.pid, a.comment, a.blocked_at, a.blocked_by, a.created_at, a.updated_at,
    b.name, b.is_moderator, COUNT(IF(d.type = 1, 1, NULL)) AS a_count, COUNT(IF(d.type = 2, 1, NULL)) AS b_count
FROM 
    comments AS a
RIGHT JOIN 
    users AS b ON b.id = a.user_id
RIGHT JOIN 
    node_user AS c ON c.user = b.id
RIGHT JOIN 
    nodes AS d ON d.id = c.node
WHERE 
    a.pid = 999
GROUP BY
    comment_id
ORDER BY
    a.created_at ASC

It gets all comments belonging to a specific pid, it then RIGHT JOINS additional user data like name and is_moderator, then RIGHT JOINS any (so called) nodes including additional data based on the user id and node id. As seen in the SELECT, I count the nodes based on their type.

This works great for users that have any nodes attached to their accounts. But users who don't have any, so whose id doesn't exist in the node_user and nodes tables, won't be shown in the query results.

So my question:

How can I make it so that even users who don't have any nodes, are still shown in the query results but with an a_count and b_count of 0 or NULL.

标签： sqlselectwhere-clauseright-join

Answer 1

I'm pretty sure you want left joins not right joins. You also want to fix your table aliases so they are meaningful:

SELECT . . .
FROM comments c LEFT JOIN
     users u
     ON u.id = c.user_id LEFT JOIN
     node_user nu
     ON nu.user = u.id LEFT JOIN
     nodes n
     ON n.id = nu.node
WHERE c.pid = 999
GROUP BY c.id
ORDER BY c.created_at ASC;

This keeps everything in the first table, regardless of whether or not rows match in the subsequent tables. That appears to be what you want.