CODEWITHSUNDEEP
mysqlsql
Vishal Arora
Vishal Arora

Reputation: 81

return multiple rows in subquery mysql

i am new in mysql and stuck on a query.i want all the records of one table and multiple tags from other table that match with one column of first table.

select A.*,(select B.tag from crm_tags B where tag_id in (A.tags)) from crm_stores A;

there are multiple stores in crm_stores and each store has multiple tags in crm_tags. i want all details of store and each tag of store in one query. when i tried the above query, it generates error : subquery returns multiple rows. please help, how to solve that problem The tags in crm_stores are like "2098,2063",means multiple tags are comma seprated. While crm_tags has seprated entries Thanks in advance

this is my first table crm_stores 
+----------+-------+--------+-----------+----------+-
| store_id | guest | budget | tags | discount |
+----------+-------+--------+-----------+----------+- 
| 23 | 5 | 1000 | 2098,2063 | 50% | 
+----------+-------+--------+-----------+----------+- 

this is my second table crm_tags
+--------+--------------+ 
| tag_id | tag | 
+--------+--------------+ 
| 2063 | Chinese | 
| 2098 | North Indian | 
+--------+--------------+

Upvotes: 2

Views: 1091

Answers (1)

niyou
niyou

Reputation: 873

use a join:

SELECT A.*, B.tag
FROM crm_stores A 
    LEFT JOIN crm_tags B ON A.tags LIKE concat(concat('%',B.tag_id),'%')

new mysql fiddle: http://sqlfiddle.com/#!9/dedeb1/7/0

Upvotes: 1

Related Questions