Self referential join
-
I have a table with a parent-child relationship in a MySQL database: id parent_id - points to id other columns I can easily extract all rows belonging to a particular parent. For example: SELECT * FROM table WHERE parent_id=5 What I'm trying to do is extend this to not only get all rows belonging to a particular parent, but also get a count of each row's children (the grandchildren). I don't need any further generations. Other than retrieving the children and iterating through the rows and fetching the counts seperatley, I've been getting into a good muddle with joins and self-referential joins. I'm hoping that some kind soul has a few suggestions!
-
I have a table with a parent-child relationship in a MySQL database: id parent_id - points to id other columns I can easily extract all rows belonging to a particular parent. For example: SELECT * FROM table WHERE parent_id=5 What I'm trying to do is extend this to not only get all rows belonging to a particular parent, but also get a count of each row's children (the grandchildren). I don't need any further generations. Other than retrieving the children and iterating through the rows and fetching the counts seperatley, I've been getting into a good muddle with joins and self-referential joins. I'm hoping that some kind soul has a few suggestions!
-
This seems to work on my system (not MySQL). SELECT * , (SELECT COUNT(*) FROM tblTasks WHERE parentTaskID=T.taskID) FROM tblTasks AS T WHERE T.parentTaskID=5; Don't know whether MySQL will support the (SELECT ...). Hope this helps.