RedBullLee
asked on
MySQL - Returning a list and count of 'sub list'?
Hi Folks,
Firstly, my apologies for the poor title of this question - I went through several ways of phrasing it and none of them were any better!
I have a very simple DB Table, as follows;
ID (INT)
Cat_Name (VAR)
Cat_Shortname (VAR)
SubcategoryOf (INT)
My Query at the moment looks like this;
SELECT *
FROM categories
WHERE SubcategoryOf=0
ORDER BY Cat_Name ASC
The '0' basically indicates its a top level category. As an example dataset, we could have the following;
ID - 1
Cat_Name - iPods
SubcategoryOf - 0
ID - 2
Cat_Name - Classic
SubcategoryOf - 1
ID - 3
Cat_Name - Touch
SubcategoryOf - 1
That data would basically indicate we have one top level category (iPods) which has 2 subcategories (Classic & Touch).
Now, my current query uses a where clause to only return the top level categories, however I would also like to return a count of the number of sub categories for each row too.
I know how to accomplish this using a left join (if the data was in a different table), but I'm not sure if its possible to do when the data is all in a single table?
Firstly, my apologies for the poor title of this question - I went through several ways of phrasing it and none of them were any better!
I have a very simple DB Table, as follows;
ID (INT)
Cat_Name (VAR)
Cat_Shortname (VAR)
SubcategoryOf (INT)
My Query at the moment looks like this;
SELECT *
FROM categories
WHERE SubcategoryOf=0
ORDER BY Cat_Name ASC
The '0' basically indicates its a top level category. As an example dataset, we could have the following;
ID - 1
Cat_Name - iPods
SubcategoryOf - 0
ID - 2
Cat_Name - Classic
SubcategoryOf - 1
ID - 3
Cat_Name - Touch
SubcategoryOf - 1
That data would basically indicate we have one top level category (iPods) which has 2 subcategories (Classic & Touch).
Now, my current query uses a where clause to only return the top level categories, however I would also like to return a count of the number of sub categories for each row too.
I know how to accomplish this using a left join (if the data was in a different table), but I'm not sure if its possible to do when the data is all in a single table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That has worked perfectly, thank you!