Parents and children

[seanf]

With a table like this:

id| name    | parent
====================
1 | parent1 | null
2 | parent2 | null
3 | child1  | 1
4 | child2  | 1
5 | child3  | 2

I want to get the following result:

parent1
- child1
- child2
parent2
- child3

I'm currently doing a LEFT JOIN on parent = id. Is there a better way to achieve what I want?

Sean

[r937]

it's not clear what you want, but yes, a left join is always a good idea <grin>

you could be looking for parents with no children

or children with their parents to the third generation (if any)

or parents with grandchildren

the specific nature of the left join depends on what you're after

as for the structure, it's fine, a classic example

[seanf]

Thank you. There will only be parents and children (i.e. only two levels), so how does this look:

SELECT cat.id, cat.name
FROM table AS cat
LEFT JOIN table AS chil ON chil.parent = cat.id

?

Sean

[r937]

that's good, as far as it goes

here's what it produces --

1 parent1
1 parent1
2 parent2

can you see why?

[seanf]

It will return a parent each time it's used, i.e. there are two children with a parent of parent1, so parent1 will be returned twice ?

Sean

[r937]

correct

if this is going too slow, let me know


without changing anything else, make this your SELECT clause --

SELECT cat.id, cat.name, chil.id, chil.name

that is the result set that you want to bring into your program or script

to get the results displayed in nested output format, i.e. header and detail lines, please see http://searchdatabase.techtarget.com...285649,00.html (free registration may be required, but go ahead, it's worth it )