tags:

views:

90

answers:

3

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?

+2  A: 

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
Eric
This is exactly what i need Eric.. Thanks for your timely HELP.
Fero
@Fero: Glad to help. Feel free to mark this as the answer, since I'm at -1 right now. It helps for those crazy Googlers.
Eric
+1 This solution works. Not sure why it was down voted earlier.
Ben Griswold
Eric.. This is my first day in stack over flow.. will u please help me how to MARK THIS AS ANSWER.
Fero
@Fero: There's a little check mark to the left of the answer, just under the arrows. Once you check that, you've marked an answer.
Eric
i did it eric..
Fero
one more query eric.. is there any possibilites to close this query
Fero
As in this question? Nope, we leave it up for the rest of the world, so they can solve the same problems you've come across.
Eric
thats fine.. thanks for ur support ERIC
Fero
A: 
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.

McAden
A: 

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
Ben Griswold
thank for ur answer ben..
Fero
You're welcome. You can up vote answers by clicking on the up arrow to the left of each answer as well. ;)
Ben Griswold
its perfectly working
Fero