On Thu, Jul 16, 2009 at 1:20 AM, Elim PDT<e...@pdtnetworks.net> 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
I hope this is not a school assignment.
What I came up with was to create a new order column that I populated
with the name of the HEAD.
Then I can order by the head, head_id, and the member_id
mysql> select t1.member_id, t1.name, t1.head_id from (
select m1.*, IF ( m2.name IS NULL, m1.name, m2.name) as groupName from
group_member as m1
left outer join group_member as m2 ON ( m1.head_id = m2.member_id )
order by groupName, m1.head_id, m1.member_id ) AS t1;
+-----------+-------+---------+
| member_id | name | head_id |
+-----------+-------+---------+
| 3 | David | NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
| 1 | Elim | NULL |
| 2 | Ann | 1 |
+-----------+-------+---------+
5 rows in set (0.01 sec)
It seemed to work without the order by member_id but I'll assume that
is a fact of the small sample size.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
