I created a table called products that looks something like this:
+----+-----------------+------------------------------+
| id | name | another information |
+----+-----------------+------------------------------+
| 1 | product1 | a |
| 2 | product2 | b |
| 3 | product3 | c |
| 4 | product4 | d |
| 5 | product5 | e |
+----+-----------------+------------------------------+
And another one called product_list that looks like this:
+----+-----------------+-----------------+-----------------+
| id | product_1 | product_2 | product_3 |
+----+-----------------+-----------------+-----------------+
| 1 | 1 | 2 | NULL |
| 2 | NULL | 2 | NULL |
| 3 | 3 | 4 | 1 |
| 4 | NULL | NULL | NULL |
| 5 | 1 | 4 | 3 |
+----+-----------------+-----------------+-----------------+
Now I want to JOIN product_list with products ON every product_ with the id of products, it would be something like this:
+----+-----------------------------+
| id | product_list |
+----+-----------------------------+
| 1 | product1,product2 |
| 2 | product2 |
| 3 | product3,product4,product1 |
| 4 | NULL |
| 5 | product1,product4,product3 |
+----+-----------------------------+
I do many tests but no one works. I think that this code is the most similar to what I need:
SELECT
pl.id,
GROUP_CONCAT(p.name)
FROM products AS p
JOIN product_list AS pl
ON(
(pl.product_1 = p.id OR pl.product_1 IS NULL)
AND (pl.product_2 = p.id OR pl.product_2 IS NULL)
AND (pl.product_3 = p.id OR pl.product_3 IS NULL)
)
GROUP BY pl.id
