Jump to content

Difficult SQL query


msxgames

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]

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.