Understanding Sub Queries

[TapeGun007]

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

[ajlisowski]

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`

[TapeGun007]

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. 

[ajlisowski]

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.