Counting children in another table for each row

Question

I have two tables: CATEGORY and SUBCATEGORY

Table Structure for CATEGORY

category_id         int(11)  
category_name       varchar(250) 
category_status     enum('0', '1')

Table Structure for SUBCATEGORY

subcategory_id      int(10) 
subcategory_name    varchar(255)  
status              enum('0', '1')

For example there is a single CATEGORY named .NET and it has entries in SUBCATEGORY like ASP.NET, VB.NET, C#.NET . In this case I need to get the count of CATEGORY as 1 and the count of SUBCATEGORY as 3 using MySQL.

How can I accomplish this?

Answer 1

Well, you can do it with a subquery. However, you'll need to add a category_id column to the subcategory table, so that we know what subcategories go with which categories. Then, you can get what you want with the following query:

select
    category_name,
    1 as CategoryCount,
    (select count(*) from subcategory where category_id = c.category_id) as SubCategoryCount
from
    category c

Answer 2

SELECT COUNT(*) FROM CATEGORY;
SELECT COUNT(*) FROM SUB_CATEGORY;

I don't believe that's exactly what you're going for, but that's all you're really gonna get without a foreign key.

Answer 3

Since we can assume category count is one and there's more than likely a key constraint on category_id between the two tables, this will work as well:

select c.category_id, count(c.category_id) 
from category c
inner join subcategory s on (c.category_id = s.category_id)
group by c.category_id