Jump to content


Photo

Difficult SQL query


  • Please log in to reply
2 replies to this topic

#1 msxgames

msxgames
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 10 February 2003 - 04:29 PM

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]

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 11 February 2003 - 08:39 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 msxgames

msxgames
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 12 February 2003 - 08:20 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users