return multiple rows in subquery mysql

Question

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 | 
+--------+--------------+

niyou · Accepted Answer · 2015-04-22 06:00:13Z

1

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

edited Apr 22, 2015 at 6:00

answered Apr 21, 2015 at 13:01

niyou

8791 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vishal Arora Over a year ago

thanks for answering it, but tags and tag_id does not match.Because tags are like "2098,2063" and tag_id like 2098

niyou Over a year ago

please provide a sample row of each table

Vishal Arora Over a year ago

+----------+-------+--------+-----------+----------+- | store_id | guest | budget | tags | discount | +----------+-------+--------+-----------+----------+- | 23 | 5 | 1000 | 2098,2063 | 50% | +----------+-------+--------+-----------+----------+- this is my first table crm_stores +--------+--------------+ | tag_id | tag | +--------+--------------+ | 2063 | Chinese | | 2098 | North Indian | +--------+--------------+ this is my second table crm_tags

Vishal Arora Over a year ago

i am running your query in mysql, and this is returning all the tags in that table, but its working fine in your demo, will you please tell where is the mistake

niyou Over a year ago

my sample was postgressql - i changed it to fit mysql

Collectives™ on Stack Overflow

return multiple rows in subquery mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related