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.

Aren't you implying an inner join here with a filter on comments? — Cetin Basoz, Sep 24 '19 at 17:46
Although you are using right joins (or left if you wish), you have a filter on comments which could be null because of (right ot left) joins. But since you are explicitly sayin it should be 999, you are saying you in fact meant inner join. — Cetin Basoz, Sep 24 '19 at 18:08
Learn what LEFT/RIGHT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right/left [sic] table column to be not NULL after a LEFT/RIGHT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Sep 24 '19 at 23:10
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. — philipxy, Sep 24 '19 at 23:10

score 1 · Accepted Answer · answered Sep 24 '19 at 17:45

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.

answered Sep 24 '19 at 17:45

Gordon Linoff

1,242,037
58
646
786

That's exactly what I needed, but seems I forgot one thing in my first post: I also need to check whether all the count `a_count` and `b_count` have an `active = 1`. But I suppose that's a matter of changing `COUNT(IF(d.type = 1, 1, NULL)) AS a_count` to `COUNT(IF(d.type = 1 AND d.active = 1, 1, NULL)) AS a_count`, right? – luikstruik Sep 24 '19 at 18:07
@luikstruik . . . Something like that should do what you want. – Gordon Linoff Sep 24 '19 at 18:14

SQL issue with RIGHT JOINS

1 Answers1