Solved

is there a way to do this in  single query?

Posted on 2012-03-29
2
430 Views
Last Modified: 2012-03-29
hello,
i have a pretty simple table.

the parent tc_group record has a parentID of 0. A tc_group can have children and that would be indicated by the parentID

CREATE TABLE `tc_group` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `userID` INT(10) UNSIGNED NULL DEFAULT NULL,
      `group_name` VARCHAR(50) NULL DEFAULT NULL,
      `parentID` INT(10) UNSIGNED NULL DEFAULT '0',
      `idx` SMALLINT NULL DEFAULT '0',
      PRIMARY KEY (`id`)


What i would like to do but can not seem to think of a way to do it is with One query get all the rows that have a parentID = 0  and a count of how many rows each of those records have were its own ID is the parentID. So get all the parents and a count of their children with a single query.

I am thinking maybe i could do this with a subselect but jsut can't seem to get my head around it
thanks for any help
0
Comment
Question by:paries
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 37784275
try this:

select g.*, (select count(1) from tc_group g2 where g2.parentID=g.id) as ChildCount
from tc_group g
where parentid=0
0
 

Author Closing Comment

by:paries
ID: 37784283
thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question