SQL help!

[alanzhao]

Hey, suppose I have the following data structure,

root_id | parent_id

| 1 | 0 |

| 2 | 1 |

| 3 | 2 |

The given root_id is 1, it's also the parent_id of the second row.

How do I select the highlighted data and return in one single row? The return value will be root_id, parent_id, third_id!???

[craigh]

select table1.root_id, table1.parent_id, table2.parent_id as third_id from table1 left join table1 as table2 on table1.root_id=table2.parent_id where table1.root_id=1

maybe :-)

[alanzhao]

but it's only one table here?

[tha_mink]

whenever you run into a situation like the one you have...It reeks of bad DB design. A better design would be to split that table into two tables. One for say "parent" and one for "child". Then you can relate the two tables. When designing your tables, you should never index a row with another row in the same table. (if you can avoid it)