Jump to content

Archived

This topic is now archived and is closed to further replies.

msxgames

Difficult SQL query

Recommended Posts

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]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.