is there a way to do this in single query?
Posted on 2012-03-29
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