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
  • 54,599
  • 15
  • 92
  • 98
  • 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
  • 267,341
  • 46
  • 444
  • 390
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
  • 15,135
  • 20
  • 70
  • 105
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,634
  • 5
  • 41
  • 61
-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,074
  • 3
  • 24
  • 32