Link to home
Start Free TrialLog in
Avatar of paries
paries

asked on

is there a way to do this in single query?

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
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada 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 paries
paries

ASKER

thanks