MySQL: Getting connected (simillar) data with lef/right fields

Question

In MySQL Im having two tables:

• PRODUCTS (id, Name)
• SEEALSO (id, prodLeft, prodRight)

SEEALSO defines which PRODUCTS are related together and are represented as binded fileds "prodLeft"-"prodRight". For Example:

PRODUCTS:

• 1 Desk
• 2 Table
• 3 Chair
• 4 Doors
• 5 Tree
• 6 Flower

SEEALSO

• 1 1 2
• 2 2 3
• 3 3 4
• 4 5 6

From that we can see binding of Desk-Table-Chair-Doors and Tree-Flower. I would now want to write SQL statement where i could specifie PRODUCT name (e.g. Chair) and i would get result of binded fields that are connected with it (e.g. Chair: Desk-Table-Chair-Doors). From this point on i would like to know if this is even possible for my data presentation concept in SEEALSO and if it is if you could help me solve my problem. tnx!

Answer 1

As you're wondering whether it's even possible, you could look into this information on Nested Sets, which is the MySQL way of doing this (I gather).

I could not give you a worked sample, as I'm no MySQL expert: perhaps this will help you enough given the general nature of your question.