Robert Brax Robert Brax - 1 month ago 6
SQL Question

Count posts threads comments but also return those with no threads

Here is as far as I could go:

SELECT link_title, count(links.unique_id) as Total
FROM links, threads
WHERE threads.links_id = links.unique_id
GROUP BY links.link_title


This will correctly return all "links" record and corresponding comments count for each record that have threads. Problem is those links with no thread are not returned at all.

What would be the simplest way to reformat this query so it will return ALL "links" even those with 0 threads.

Answer

You want an outer join (which is impossible to write with the ancient implicit join in the where clause you are using):

SELECT link_title, count(threads.links_id) as Total 
FROM links 
  LEFT JOIN threads ON threads.links_id = links.unique_id 
GROUP BY links.link_title

Because aggregate functions like count() ignore NULL values, it's important to count the number of rows in the threads table by using count(threads.links_id) as threads.links_id will be NULL for links that don't have threads

Comments