Greetings,
I'm working on part of an application where I need to build a list 'Parent' categories and which each parent category a sub listing of how many 'Child' categories are underneath them, as well as how many total entries fall under the linked child categories.
It's for a more generic CMS, but for now lets say it's being used for an image gallery (which it will be if all goes to plan). I'd like to list my main categories (General, News, Sports, etc... for example) as well as inform the reader how many sub categories (Landscape, Still Life, etc.. for example) are under each main category, and further how many entries (images) exist in total under the main category.
Hopefully that made some sense, if not let me know... for now on to the query I'm using:
select parent_name, sg_parent_categories.parent_id, count(entry_id) as entry_count, max(entry_date) as last_update, count(child_id) as category_count
from sg_entries
inner join sg_child_categories on sg_entries.entry_child_category = sg_child_categories.child_id
inner join sg_parent_categories on sg_child_categories.parent_id = sg_parent_categories.parent_id
group by sg_parent_categories.parent_id asc;
It's pulling together the three tables involved (entries, parent_categories, and child_categories). As of now the query succesfully groups into parent categories, shows the correct number of entries under them, and even does the correct last 'update'. However, the count for the child categories under the parent does not return as expected. It returns the same value as the number of entries under the parent.
So I'm guessing that the count is simply returning the number of rows in the group rather than the number of unique child_id's. How do I get it to count the number of child cats under the parent?
If it proves helpful to seeing a problem, here are the tables used:
create table sg_entries (
entry_id mediumint not null primary key auto_increment,
entry_title varchar(30) not null unique,
entry_month varchar(10) not null,
entry_year year(4) not null,
entry_child_category mediumint not null,
entry_printable enum('Y','N') not null,
entry_image mediumint not null,
entry_date varchar(15) not null
);
create table sg_parent_categories (
parent_id mediumint not null primary key auto_increment,
parent_name varchar(20) not null unique,
parent_description tinytext not null
);
create table sg_child_categories (
child_id mediumint not null primary key auto_increment,
child_name varchar(20) not null unique,
child_description tinytext not null,
parent_id mediumint not null
);
Feel free to also poke fun and/or offer tips if you see that the structure of the tables could be improved.
Thanks in advance!
