JOIN 2 tables based by multiple ids mysql

Question

I have 2 tables let's say orders

id | memberid | productsid |
----------------------------
 1 |   23     |   25,27

and products

 id | product_name | price |
----------------------------
 25 |   product1   |   120
 27 |   product2   |   50

I want to join orders and products table to get product name and price for each id from productsid. This is the way how I tried to store an order for a member. If you have any better solution, I am waiting to know.

Answer 1

You can join the tables with the help of the function find_in_set(), then group by each order and with group_concat() create a list of the products:

select o.id, o.memberid,
  group_concat(p.product_name order by find_in_set(p.id, o.productsid)) products
from orders o inner join products p
on find_in_set(p.id, o.productsid)
group by o.id, o.memberid

See the demo.
Results:

| id  | memberid | products          |
| --- | -------- | ----------------- |
| 1   | 23       | product1,product2 |