mysql query for getting a list of items with reference to another table

Question

i have table like this

+----+-------+-------------+
| id | items |     name    |
+----+-------+-------------+
|  1 | 1,2,3 |   hot deals |
|  2 | 2,3,4 |   offers    |
+----+-------+-------------+

where items are the id of product

here is the product table (mini version)

+-----------+--------------------+
| productid |  productname       |
+-----------+--------------------+
|         1 |   sony mobile      |
|         2 |   iphone 4s        |
|         3 |   dell laptop v1   |
|         4 |   samsung mouse    |
+-----------+--------------------+

i need to get result as array of items with name ex [hot deals =>[(1,sony mobile),(2,iphone 4s),(3,dell laptop v1 ) ],offers=>[--]]

can achieve this in (MYSQL)stored procedure if not how can i achieve this with minimum numberof queries side note:first table will have maximum of 6 rows

Answer 1

You can use a join with find_in_set():

select t.id, t.name, p.productname
from yourtable t
   join products p on find_in_set(p.productid, t.items)

• SQL Fiddle Demo

As a side note, I'd recommend looking into database normalization. Better to create another 1-n table to store the comma separated values separately.