parent/child relationship in the same table.

From: Jason Diamond <jason_at_injektilo.org>
Date: 15 Nov 2001 20:11:34 -0800
Message-ID: <6768b874.0111152011.22f6087b_at_posting.google.com>

Hi.

I'm trying to create a table where each row can be the "child" of another row in the same table. Or in other words, a row could be the "parent" of multiple "child" rows. This sounded like a one-to-many relationship to me so I added a parent column to the table. Here's a simple example table:

create table test (id int, parent int, description varchar(256))

I thought that I could assume that a row is not a child if it's parent value was 0. Here's some sample data:

insert into test (id, parent, description) values (1, 0, 'one')
insert into test (id, parent, description) values (2, 0, 'two')
insert into test (id, parent, description) values (3, 1, 'child of

one')
insert into test (id, parent, description) values (4, 0, 'four') insert into test (id, parent, description) values (5, 3, 'child of child of one')

When I execute a simple select on the table I get my data back in the order I entered it:

select * from test

returns:

1, 0, 'one'
2, 0, 'two'
3, 1, 'child of one'
4, 0, 'four'
5, 3, 'child of child of one'

But what I really want is to get back each row with it's children immediately following it like this:

1, 0, 'one'
3, 1, 'child of one'
5, 3, 'child of child of one'
2, 0, 'two'
4, 0, 'four'

I can't think of any way to do this with a simple order by clause--there's obviously no columns that are ordered in my desired results.

Is there a better way to model this so that I can get the ordering I want with a single query?

Thanks,
Jason. Received on Fri Nov 16 2001 - 05:11:34 CET