get nested related products mysql

Question

I have two tables to related products with each other.

product
---------------------------------------------
product_id | Name | creation_date
---------------------------------------------
    1          A           2017-03-11 11:30:13
    2          B           2017-03-12 12:30:12
    3          C           2017-03-13 13:30:45
    4          D           2017-03-14 14:30:45
    5          E           2017-03-15 15:30:45

product_related
---------------------------------------------
related_id | product_id | related_product_id
---------------------------------------------
    1            1               2
    2            2               5
    3            3               1
    4            4               3

I need to get all related products with each other indirectly or directly.

If @ProductID = 1, then I need Product ID's 2,3,4,5 

If @ProductID = 2, then I need Product ID's 1,3,4,5

If @ProductID = 3, then I need Product ID's 1,2,4,5 

If @ProductID = 4, then I need Product ID's 1,2,3,5

If @ProductID = 5, then I need Product ID's 1,2,3,4

I am fine if need to change in product_related table design to increase the execution performance.

@strawberry, Yes I accept requirement is complex but this is what I need — Jitesh T, Mar 12 '17 at 09:41
I don't mind if there are any changes needed in my related structure — Jitesh T, Mar 12 '17 at 09:44

score 0 · Answer 1 · edited May 23 '17 at 12:25

You have tagged this as php, so I suspect there are other issues that you are about to experience :)

What you need is recursive queries in MySQL. I'm not a MySQL developer but for other punters out there who see this post and use MS SQL, this is pretty simple. In MS SQL we use a recursive CTE (common table expression) to simplify this deep linking.

Please follow this article for guidance: How to do the Recursive SELECT query in MySQL?

get nested related products mysql

1 Answers1