3

My question is a little bit complicated, that's why I will use images to explain it: Actually, I am trying to have a kind of nested structure for a GUI, using a MySQL database with the table below:enter image description here

This table could be shown as: enter image description here

and what I am trying to do is to show only the elements, that has at least one green element as a child (could be not a direct child), as shown below: enter image description here I know that Recursive Queries in MySQL doesn't exist, and MySQL is not Hierarchical, even if it could be simulated as explained here . Does anyone have any idea? (The number of levels is not fixe)

simo
  • 91
  • 1
  • 7
  • Is MySQL the only tool you have? Or are you performing these queries from an application? Because if you are using an application (well, developing one) you could simulate these recursive queries. – Michael De Keyser Mar 12 '13 at 10:11
  • 2
    Only really 2 MySQL solutions. Either use a stored procedure to loop down through the child records of a record looking for a matching one. Or you use a nested set model (works well, but my experience is it struggles when inserting / deleting lots of records). – Kickstart Mar 12 '13 at 10:15
  • MySQL is all I have, so I think I will use a stored procedure as proposed by Kickstart. Thanks! – simo Mar 12 '13 at 10:32
  • 1
    You can't write GUI programs with only mysql - you MUST have other tools at your disposal. – symcbean Mar 12 '13 at 13:07
  • Of course, but I mean MySQL is the only choice I have for the DB, I am using PHP, JQUERY... – simo Mar 12 '13 at 13:17

1 Answers1

0

Unless you have extremely restricted in your technologies, use other technologies than database-native ones for such algorithmic tasks for applications.

Your particular case sounds like Nested Set Model, see more examples and discussions on SO:

Community
  • 1
  • 1
Olha Puzhay
  • 370
  • 2
  • 9