How to add a LEFT JOIN to this MySQL Query?

Question

Hi! I'm quite a beginner at this so please forgive me if this seems a bit easy for some of you.

So, I have this query here:

SELECT code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date,
                    code_tags.*, 
                    tags.*, 
                    users.firstname AS authorname, 
                    users.id AS authorid,
                    GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup
                    FROM code, code_tags, tags, users
                    WHERE users.id = code.author AND code_tags.code_id = code.id AND tags.id = code_tags.tag_id
                    GROUP BY code_id
                    ORDER BY date DESC

Pretty intense. I want to count the number of comments a code submission has from the table comments. I can't add it as a WHERE comments.codeid = code.id because then that won't select submissions from the database without comments.

How can I add in something along the lines of LEFT JOIN comments.codeid ON code.id = comments.codeid or something along those lines?

Thanks!

Jack

Answer 1

Change your existing joins to be proper SQL-92 JOINS, and adding the left joins becomes trivial.

SELECT 
  code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date,
  code_tags.*, 
  tags.*, 
  users.firstname AS authorname, 
  users.id AS authorid,
  GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup
FROM 
  code
  join code_tags on code_tags.code_id = code.id
  join tags on tags.id = code_tags.tag_id
  join users on users.id = code.author
  left join comments on comments.codeid = code.id
GROUP BY code_id
ORDER BY date DESC

Note that as a general rule-of-thumb, selecting * in a query is considered a bad idea. Only retrieve the columns that you actually need.

Answer 2

Use count(comments.code_id) and left join to get the number of comments. For that to work, you need to group by all the returned columns except comments.code_id Some databases (eg MS Access) can't group on fields that are selected with *, so you will have to select them by name and then include them in the group by.

eg if table code_tags has fields code_id and tag_id and table tags has fields id and tag then it should look like:

SELECT code.id AS codeid, code.title AS codetitle,  
code.summary AS codesummary, code.author AS codeauthor,  
code.rating AS rating, code.date,  
code_tags.code_id, code_tags.tag_id, tags.id, tags.tag,  
users.firstname AS authorname, users.id AS authorid,  
GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup  
count(comments.code_id) as NumComments  
FROM  
code JOIN code_tags ON code_tags.code_id = code.id  
JOIN tags ON tags.id = code_tags.tag_id  
JOIN users ON users.id = code.author  
LEFT JOIN comments ON comments.code_id = code.id  
GROUP BY code_id, codetitle, codesummary, codeauthor, rating, code.date,  
code_tags.code_id, code_tags.tag_id, tags.id, tags.tagName,
authorname, authorid, taggroup
ORDER BY date DESC