Link to home
Start Free TrialLog in
Avatar of RedBullLee
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?
ASKER CERTIFIED SOLUTION
Avatar of rushShah
rushShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RedBullLee
RedBullLee

ASKER

Apologies for the delay replying, rushShah.

That has worked perfectly, thank you!