Problem with COUNT () when joining tables

Operandi

Hello

I have two tables:

CREATE TABLE mnl_categories (
  cat_id int(11) NOT NULL auto_increment,
  category varchar(100) NOT NULL default '',
  description varchar(200) default NULL,
  score_1_name varchar(50) default NULL,
  score_2_name varchar(50) default NULL,
  score_3_name varchar(50) default NULL,
  score_4_name varchar(50) default NULL,
  active char(3) NOT NULL default 'yes',
  PRIMARY KEY  (cat_id),
  KEY active (active)
) TYPE=MyISAM;


CREATE TABLE mnl_sub_categories (
  sub_cat_id int(11) NOT NULL auto_increment,
  cat_id int(11) NOT NULL default '0',
  sub_category varchar(100) NOT NULL default '',
  description varchar(200) default NULL,
  active char(3) NOT NULL default 'yes',
  PRIMARY KEY  (sub_cat_id),
  KEY cat_id (cat_id)
) TYPE=MyISAM;

I want to show a list of categories with a count of each sub_category for that list ie

Restaurant (5)
Diner (0)
Fast Food (3)

This is the query that I am using:

"SELECT mnl_categories.cat_id,
            mnl_categories.category,
            mnl_sub_categories.sub_category,
            Count( mnl_sub_categories.sub_category ) As number_sub_cats 
            FROM mnl_categories 
            LEFT JOIN mnl_sub_categories ON mnl_categories.cat_id = mnl_sub_categories.cat_id
            WHERE mnl_categories.active='yes' AND mnl_sub_categories.active='yes'
            GROUP BY mnl_categories.category
            ORDER BY mnl_categories.category";

It returns the count of every category that has a sub category, but will not return a category that has no sub categories ie Diner (0)

I'm stumped!

Any suggestions please?

bogu

There is no problem with your sql or your tables, and for what you want is your sql should look like this

"SELECT mnl_categories.category, Count( mnl_sub_categories.sub_category )
As number_sub_cats
FROM mnl_categories
LEFT JOIN mnl_sub_categories ON mnl_categories.cat_id = mnl_sub_categories.cat_id
WHERE mnl_categories.active = 'yes' AND mnl_sub_categories.active = 'yes'
GROUP BY mnl_categories.category
ORDER BY mnl_categories.category"

Operandi

There is no problem with your sql or your tables,

Thanks for the reply, bogu, however it still does not work properly in that the category records are displayed and the count works when there is a sub category, but the category is not listed if there is no related sub category

Lars_Berg

Putting restrictions on the right table in the where clause reverts the outer join to an inner join.

SELECT mnl_categories.category, Count( mnl_sub_categories.sub_category )
As number_sub_cats
FROM mnl_categories
LEFT JOIN mnl_sub_categories 
ON mnl_categories.cat_id = mnl_sub_categories.cat_id
AND mnl_sub_categories.active = 'yes'
WHERE mnl_categories.active = 'yes' 
GROUP BY mnl_categories.category
ORDER BY mnl_categories.category

nnichols

Try this. I have tested it with some dummy data and it works fine -

SELECT mnl_categories.cat_id,
	mnl_categories.category,
	mnl_sub_categories.sub_category,
	SUM( IF( mnl_sub_categories.active='yes',1,0 ) ) As number_sub_cats 
	FROM mnl_categories 
	LEFT JOIN mnl_sub_categories ON mnl_categories.cat_id = mnl_sub_categories.cat_id
	WHERE mnl_categories.active='yes'
	GROUP BY mnl_categories.category
	ORDER BY mnl_categories.category

Hope this helps 😉

Operandi

Hope this helps

It sure did!

Thanks very much.

I've got a similar three table query like this to design now so I may need more help!🙁

nnichols

I just checked my solution against Lars' solution. His is far more efficient and should be effectively optimized internally whereas mine will not.

Conclusion - when the tables get big you will get significant performance differences, so use Lars' solution, not mine.

If the tables are small (<10,000 records) you shouldn't see a significant difference in performance.

Try them both and see. Sorry I didn't read Lars' solution properly first time around.

Operandi

Thanks to both Lars and nnichols for helping me resolve this. As nnichols suggested, I used Lars solution in the end.