TapeGun007 Posted March 15, 2010 Share Posted March 15, 2010 And maybe sub queries aren't the answer. I'm just trying to figure out if I can just take care of this in mySQL rather than manipulating an array of strings or something. Table: Groups Group_ID | Group_Name | Parent_Group_ID --------------------------------------------------------- 1 Science Null 2 Math Null 3 History Null 4 Algebra 2 5 Geometry 2 6 Calculus 2 7 Art Null There are two groups here in this table, Parent Groups, and Sub Groups. Math is a parent group. Alegebra, Geometry, and Calculus are sub groups of the parent group Math. I want to create a table that would look like this, but I need the SQL (if possible) to make it come in this order. Output: Art History Math Algebra Calculus Geometry Science Link to comment https://forums.phpfreaks.com/topic/195265-understanding-sub-queries/ Share on other sites More sharing options...
ajlisowski Posted March 15, 2010 Share Posted March 15, 2010 SELECT `Group_ID, `Group_Name`, `Parent_Group_ID` FROM `group` ORDER BY `Group_Name` you would not need a subquery... However, if you wished to have the parent group name, instead of the ID, you could do this... SELECT g.`Group_ID, g.`Group_Name`, p.`Group_Name` AS `Parent_Group_Name` FROM `group` AS `g` LEFT JOIN `group` AS `p` ON g.`Parent_Group_ID`=p.`Group_ID` ORDER BY g.`Group_Name` Link to comment https://forums.phpfreaks.com/topic/195265-understanding-sub-queries/#findComment-1026385 Share on other sites More sharing options...
TapeGun007 Posted March 15, 2010 Author Share Posted March 15, 2010 I don't want to order by the Group Name though, I want it to order by the group name alphabetically, but if there's a sub group, put that under the main group in alphabetical order. The "Output" listed above is what I want it to do. Link to comment https://forums.phpfreaks.com/topic/195265-understanding-sub-queries/#findComment-1026413 Share on other sites More sharing options...
ajlisowski Posted March 15, 2010 Share Posted March 15, 2010 ah, gotcha... thats a bit trickier SELECT g.`Group_ID`, g.`Group_Name`, IF(g.`Parent_Group_ID` !=0,p.`Group_Name`,g.`Group_Name`) AS `parent_group` FROM `group` AS `g` LEFT JOIN `group` AS `p` ON g.`Parent_Group_ID`=p.`Group_ID` ORDER BY `parent_group`, g.`Parent_Group_ID`, g.`Group_Name` Basically, it should set the parent name equal to the current name if it doesnt have a parent. Then order by the parent name and the parent ID and the group_name. This, however, will likely not work if you have multiple layers of parents. For example Science-Biology-Genetics. It will be thrown off because it will put genetics ordered by Biology and not science. However, for a simple 1 layer of parent-child it should work. Link to comment https://forums.phpfreaks.com/topic/195265-understanding-sub-queries/#findComment-1026426 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.