Multiple Queries into 1 Query Question

[darknight0]

I was wondering if someone could help me in a dilemma that I’m having. I’m trying to put like three queries results into one query and those queries depend on each other’s results and the

results are in one table.

Here is what I am trying to do. I’ve a table with the following structure:
Table Object:
ID = int autoincrement – Primary Key
Title = text
Parent_ID = int

The purpose of this table is to make a tree like structure where there is a root node (object) and all the other nodes are child nodes of the root node and may have child nodes, which the child nodes may have their own child nodes. I’ll try to sketch it below:

0 (ID)
|
1------2
| |
3 4---5
| | |
6-7 8 9

So if you look at it. I'm looking for my siblings and my siblings children and my own children. So my dilemma is can I do this in ONE query.

Any help would be appreciated.

 

[swampBoogie]

If you want to handle an arbitrary depth in the tree it is not possible (with Mysql).

If you limit yourself to handle e.g two levels you can do as

select title from object where parent_ID is null
union all
select s.title from object s , object p
where p.parent_id is null
  and s.parent_ID = p.id
union all
select gs.title from object gs, object s, object p
where p.parent_id is null
  and s.parent_ID = p.id
  and gs.parent_ID = s.id

 

[darknight0]

Thanks SwampBoogie.

That helped me.

Thank you.