On Thu, Jul 16, 2009 at 1:20 AM, Elim PDT<elim@stripped> wrote:
> My table group_member looks like this:
> +-----------+-------+---------+
> | member_id | name | head_id |
> +-----------+-------+---------+
> | 1 | Elim | NULL |
> | 2 | Ann | 1 |
> | 3 | David | NULL |
> | 4 | John | 3 |
> | 5 | Jane | 3 |
> +-----------+-------+---------+
>
> Record with null head_id means
> the member is a group head.
> Record with head_id k are in the
> group with head whoes id equals k.
>
> I like to fetch the rows in the following ordaer
>
> | 3 | David | NULL |
> | 4 | John | 3 |
> | 5 | Jane | 3 |
> | 1 | Elim | NULL |
> | 2 | Ann | 1 |
>
> That is
> (1) A head-row follewed by the group members with that head
> (2)head rows are ordered alphabetically by name.
>
> What the query looks like?
>
> Thanks
>
>
You need to create your own sort values, and link to the "head" name.
So really you are sorting on head name + head_id. Since sometimes the
"head" name is the current record, sometimes it's a "parent" record,
you need to conditional check which type of "record" it is and built
the sort value.
SELECT tablename.*,
IF(tablename.head_id=NULL,
CONCAT(tablename.name, tablename.member_id),
CONCAT(heads.name, tablename.head_id)
) AS SortValue
FROM tablename LEFT JOIN tablename AS heads ON tablename.head_id=heads.member_id
ORDER BY SortValue
Brent Baisley
