List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:May 29 1999 12:06am
Subject:Re: query structure approach question
View as plain text  
Pat Trainor wrote:
> 
>         This is 1/2 MySQL, and 1/2 Perl.. there isn't much left in life
> after that split..
> 
>         I am interested in what you all would say about how to approach
> this:
> 
> given: latest mysql and perl and apache...
> 
>         There is a single table with 2 columns: id and parent. Each parent
> entry is the id of another row. One parent can have many children. The id
> is unique. There are children of children, etc..
> 
>         Now, here's what is desired: a way to query so that a tree is
> produced that shows all ids, and their relationship to their parent. I'll
> cgi it somehow, but the hard part _seems_ to be how to write a loop that
> will keep track of what _generation_ the child is, etc..
> 
>         Ideas?
> 

Here's one, for what it's worth!

Add another column that will be 1 or 0 to show this record is a 2nd
generation:
mysql> SHOW FIELDS FROM tree;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| parent | int(11)    |      |     | 0       |                |
| child  | int(11)    |      | PRI | 0       | auto_increment |
| SecGen | tinyint(4) |      |     | 0       |                |
+--------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Then you need to know the max number of generations in your table:
mysql> SELECT DISTINCT parent FROM tree WHERE SecGen > 0;
+--------+
| parent |
+--------+
|      0 |
|      1 |
|      2 |
|      3 |
+--------+
4 rows in set (0.00 sec)

Use the number of returned rows (in this case 4) to create the query:
mysql> SELECT t0.parent, t0.child, t1.child, t2.child FROM tree AS t0
    -> LEFT JOIN tree AS t1 ON t1.parent = t0.child
    -> LEFT JOIN tree AS t2 ON t2.parent = t1.child
    -> WHERE t0.parent > 0 AND t0.SecGen ORDER BY t0.parent;
+--------+-------+-------+-------+
| parent | child | child | child |
+--------+-------+-------+-------+
|      1 |    25 |    34 |    36 |
|      1 |    25 |    34 |    37 |
|      1 |    25 |    35 |  NULL |
|      1 |    26 |  NULL |  NULL |
|      1 |    27 |  NULL |  NULL |
|      1 |    28 |  NULL |  NULL |
|      2 |    29 |  NULL |  NULL |
|      2 |    30 |  NULL |  NULL |
|      2 |    31 |  NULL |  NULL |
|      3 |    32 |  NULL |  NULL |
|      3 |    33 |  NULL |  NULL |
+--------+-------+-------+-------+

A quick loop thru perl or C and Presto! :)

  jim...

PS. If you want everyone whether or not they're part of a 'family':
mysql> SELECT t0.parent, t0.child, t1.child, t2.child FROM tree AS t0
    -> LEFT JOIN tree AS t1 ON t1.parent = t0.child
    -> LEFT JOIN tree AS t2 ON t2.parent = t1.child
    -> WHERE (t0.parent > 0 AND t0.SecGen) OR
    -> (t0.parent = 0 AND t0.SecGen = 0) ORDER BY t0.parent;
+--------+-------+-------+-------+
| parent | child | child | child |
+--------+-------+-------+-------+
|      0 |    38 |  NULL |  NULL |
|      1 |    25 |    34 |    36 |
|      1 |    25 |    34 |    37 |
|      1 |    25 |    35 |  NULL |
|      1 |    26 |  NULL |  NULL |
|      1 |    27 |  NULL |  NULL |
|      1 |    28 |  NULL |  NULL |
|      2 |    29 |  NULL |  NULL |
|      2 |    30 |  NULL |  NULL |
|      2 |    31 |  NULL |  NULL |
|      3 |    32 |  NULL |  NULL |
|      3 |    33 |  NULL |  NULL |
+--------+-------+-------+-------+
Thread
query structure approach questionPat Trainor29 May
  • Re: query structure approach questionJim Faucette29 May