0

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?

Eric
  • 92,005
  • 12
  • 114
  • 115
Fero
  • 12,969
  • 46
  • 116
  • 157
  • Huh? This is confusing. Please show your table schema using SHOW CREATE TABLE. Here are some useful tips: http://stackoverflow.com/questions/1204402/how-do-i-ask-for-help-optimizing-fixing-queries-in-mysql – hobodave Aug 11 '09 at 04:54
  • Whoa Eric, wtf? You seem to have gone a bit overboard with the edits there. Why did you change his intro and remove the beginner tag? – hobodave Aug 11 '09 at 05:07
  • @hobodave: I think the `beginner` tag is superfluous and nearly meaningless. If you'd like to have a discussion as to why, head on over to Meta. Aside from that, I changed the question to fix grammar. Also noticed that I missed a lingering "am," so I'll change that, too. "I am a newbie" really has no bearing on answering the question. – Eric Aug 11 '09 at 05:15
  • Just seems obtuse to remove someone's greeting, as well as the thanks at the end. – hobodave Aug 11 '09 at 05:19
  • @hobodave: See Meta for more info on it: http://meta.stackexchange.com/questions/2950 – Eric Aug 11 '09 at 05:24

3 Answers3

3

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
  • 92,005
  • 12
  • 114
  • 115
  • This is exactly what i need Eric.. Thanks for your timely HELP. – Fero Aug 11 '09 at 05:28
  • @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 Aug 11 '09 at 05:30
  • +1 This solution works. Not sure why it was down voted earlier. – Ben Griswold Aug 11 '09 at 05:37
  • Eric.. This is my first day in stack over flow.. will u please help me how to MARK THIS AS ANSWER. – Fero Aug 11 '09 at 05:47
  • @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 Aug 11 '09 at 05:48
  • one more query eric.. is there any possibilites to close this query – Fero Aug 11 '09 at 05:52
  • 1
    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 Aug 11 '09 at 06:00
1

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
  • 17,793
  • 14
  • 58
  • 60
0
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
  • 13,714
  • 5
  • 37
  • 63