Difficult SQL query

[msxgames]

I have a table \"cars\" like this example:

 

ID name parent description

1 VOLVO (NULL) Volvo

3 70 2 Volvo S70

6 C 1 Volvo C?

2 S 1 Volvo S?

5 60 2 Volvo S60

4 60 6 Volvo C60

 

There is a parent/child relationship like this:

 

Volvo (parent)

-- S (1st level child)

----60 (2nd level child)

----70 (2nd level child)

-- C (1st level child)

----60 (2nd level child)

 

This is actually the order I also would like to end up with, so:

 

ID parent

1 (NULL)

2 1

5 2

3 2

6 1

4 6

 

Can anyone tell me how my SQL query should look like:

 

SELECT ID, parent FROM cars ORDER BY ????

 

Or any other solution with UNIONS or JOINS? Thx.

[/b]

[pallevillesen]

Hmm... that is going to be ugly.

 

Let\'s start slowly:

 

1. Top level parents

 

select ID where parent IS NULL;

 

returning ID 1 only...

 

Now join this with the table again creating NULL values for all first level children... JOIN ON PARENT!

 

Returning

 

ID name parent description ID2

1 VOLVO (NULL) Volvo NULL

3 70 2 Volvo S70 NULL

6 C 1 Volvo C? 1

2 S 1 Volvo S? 1

5 60 2 Volvo S60 NULL

4 60 6 Volvo C60 NULL

 

2. 1st level children

 

select ID from joined where ID2 IS NOT NULL;

 

Anf then you may work your way through like this....

 

BUT: I don\'t like this solution, but I fear there is no simple nicer solution. You want subselects but they do not exist in mysl 3.23 - try version 4 or use temporary tables like crazy...

 

It would be a lot easier to grab the result and then parse it in php to get what you want.

 

P.

[msxgames]

Thx for the answer.

 

I already solved this issue in PHP, but this solution is quite ugly. I was hoping to use one (difficult?) query.