| re: parent-child sophisticated SQL query?
"ahaideb" <moimsd@yahoo.com> wrote in message
news:954e78ea.0406091610.5fcafea7@posting.google.c om...[color=blue]
> I have a table (relation) in my database:
>
> ---------------
> | parent | child |
> ---------------
> | 1 | 2 |
> | 1 | 3 |
> | 2 | 4 |
> | 2 | 5 |
> | 3 | 6 |
> | 5 | 7 |
> | 5 | 8 |
> ---------------
>
> This table reflect the relation between two persons, the first column
> is the parent, the second column is the child, the a child could be a
> parent to other childs
> Is it possible to construct a SQL query to start from a parent and get
> all his siblings, I was able to get the direct children of a parent
> but not all his siblings, for example to get the direct children of
> parent 1 I used the following query:
> select child from relation where parent=1.[/color]
create table relation (
rid int(11) NOT NULL auto_increment,
parent int,
child int,
PRIMARY KEY (rid)
)
insert into relation (parent,child) values
(1,2),(1,3),(2,4),(2,5),(3,6),(5,7),(5,8)
select
r1.parent,r1.child,
r2.parent,r2.child,
r3.parent,r3.child,
r4.parent,r4.child
from relation r1
left join relation r2 on (r1.child=r2.parent)
left join relation r3 on (r2.child=r3.parent)
left join relation r4 on (r4.child=r4.parent);
+--------+-------+--------+-------+--------+-------+--------+-------+
| parent | child | parent | child | parent | child | parent
| child |
+--------+-------+--------+-------+--------+-------+--------+-------+
| 1 | 2 | 2 | 4 | NULL | NULL | NULL
| NULL |
| 1 | 2 | 2 | 5 | 5 | 7
| NULL | NULL |
| 1 | 2 | 2 | 5 | 5 | 8
| NULL | NULL |
| 1 | 3 | 3 | 6 | NULL | NULL |
NULL | NULL |
| 2 | 4 | NULL | NULL | NULL | NULL | NULL | NULL
|
| 2 | 5 | 5 | 7 | NULL | NULL | NULL
| NULL |
| 2 | 5 | 5 | 8 | NULL | NULL | NULL
| NULL |
| 3 | 6 | NULL | NULL | NULL | NULL | NULL | NULL
|
| 5 | 7 | NULL | NULL | NULL | NULL | NULL | NULL
|
| 5 | 8 | NULL | NULL | NULL | NULL | NULL | NULL
|
+--------+-------+--------+-------+--------+-------+--------+-------+
10 rows in set (0.00 sec)
There's a pattern in the query. I not sure how to check indefinitely
without adding more of the pattern.
Regards,
Mike Chirico
| 
