1

I am trying to run this query however it's only outputting results of categories that have items in them, rather than all the categories. This is my query:

SELECT categories.`id` as `cat_id`, categories.`title` as `cat_title`, COUNT(tutorials.`id`) as `total`
FROM `tutorial_categories` as `categories`, `tutorials`
WHERE tutorials.`category` = categories.id
GROUP BY `cat_id`

Can anyone lead me in the right direction here? Thanks.

Jacob Talbot
  • 91
  • 7

5 Answers5

2
SELECT 
categories.id , 
categories.title,
COUNT(*) as total
FROM tutorial_categories as categories
LEFT JOIN tutorials
on tutorials.category = categories.id
GROUP BY categories.id
Nicola Cossu
  • 52,276
  • 15
  • 91
  • 96
  • You wouldn't be able to direct me as to how I could get the latest ID from the `tutorials` table as well for each category? – Jacob Talbot Dec 06 '11 at 14:24
  • There are a lot of questions about this problem. This is just an example. http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group Open a new thread if it doesn't solve. Bye. – Nicola Cossu Dec 06 '11 at 14:30
0

You need to use a LEFT JOIN. Your implicit join syntax produces an inner join insetad:

SELECT 
  categories.`id` as `cat_id`, 
  categories.`title` as `cat_title`, 
  COUNT(tutorials.`id`) as `total`
FROM
  `tutorial_categories` `categories` LEFT JOIN `tutorials`  ON categories.id = tutorials.category
GROUP BY `cat_id`
Michael Berkowski
  • 260,803
  • 45
  • 432
  • 377
0

You are looking for an outer join as described here : "An outer join does not require each record in the two joined tables to have a matching record"

Barth
  • 14,295
  • 20
  • 67
  • 102
0

You need to restyle the query using an outer join, instead of using the WHERE

Something like this

SELECT categories.`id` as `cat_id`, categories.`title` as `cat_title`, COUNT(tutorials.`id`) as `total`
FROM `tutorial_categories` as `categories`, `tutorials`
LEFT OUTER JOIN tutorials.`category` = categories.id
GROUP BY `cat_id`
Dan Kelly
  • 2,568
  • 5
  • 41
  • 59
-2

You have a WHERE tutorials.category= categories.id set so it would only return the rows that meet that criteria. Remove that and it should return "all the categories".

SELECT categories.`id` as `cat_id`, categories.`title` as `cat_title`, COUNT(tutorials.`id`) as `total`
FROM `tutorial_categories` as `categories`, `tutorials`
GROUP BY `cat_id`
Robert
  • 3,066
  • 3
  • 23
  • 32