0

I have this table:

Table ___Products

|--------|----------|----------------|
| PRO_Id | PRO_Name | PRO_LinkedProd |
|--------|----------|----------------|
|      1 |   Banana |          2,3,4 |
|      2 |    Apple |            1,3 |
|      3 |     Pear |              3 |
|      4 |   Cherry |          1,2,3 |
|--------|----------|----------------|

I'm looking to loop in PRO_LinkedProd for one product (Ex: Banana).

Desired output for Banana should be:

|--------|----------|
| PRO_Id | PRO_Name |
|--------|----------|
|      2 |    Apple |
|      3 |     Pear |
|      4 |   Cherry |
|--------|----------|

This is what I have tried so far:

SELECT *, IFNULL(GROUP_CONCAT(PRO_LinkedProd), ",") as list_prods 
FROM ___Products p 
INNER JOIN ___Products p ON p.PRO_LinkedProd like concat("%", p.PRO_Id, "%")
GROUP BY PRO_Id
ORDER BY PRO_Id ASC
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
miami
  • 49
  • 5
  • 2
    It would be best to redesign the table and NOT use a delimited list instead of a proper Foreign key. – RiggsFolly Jan 25 '23 at 15:09
  • 1
    Try looking for a beginners guide to Relational database design – RiggsFolly Jan 25 '23 at 15:11
  • 1
    @RiggsFolly I agree that the way these tables are set up isn't the right way, but the question is still valid and the link with the purported 'already answered' solution is NOT valid. You don't always know what someone may encounter on preexisting tables and I'm interested in know a solution as well. – Altimus Prime Jan 25 '23 at 15:28
  • @miami, If you're stuck with the set up you have, you should simply do two queries. `select PRO_LinkedProd from ___Products where PRO_Id =1` and then query using `where in ?` where ? is bound to the PRO_LinkedProd from the first request. – Altimus Prime Jan 25 '23 at 15:34
  • You may also check and see if this helps you, so that you can split the linked ids column and query the resulting table. https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows – Altimus Prime Jan 25 '23 at 15:44

0 Answers0