MySQL innerjoin on an array field

Question

i have 2 tables

[series]

--------------
ID | ART
--------------
1  | sculptor      
2  | painter
3  | writer
-----------

[artists]

--------------
NAME | ART_IDs
--------------
john | 1
jack | 1,2
jill | 2,1
jeff | 3,1

which I want to join like this:

SELECT se.art, ar.name as artist
FROM series AS se
JOIN artists AS ar ON FIND_IN_SET(se.id , ar.art_ids) > 0

What I get is only the first values:

[result]

-------------------
ART      | ARTISTS
-------------------
sculptor | john
sculptor | jack
painter  | jill
writer   | jeff

Instead of:

[result]

----------------------------
ART               | ARTISTS
----------------------------
sculptor          | john
sculptor,painter  | jack
painter,sculptor  | jill
writer,sculptor   | jeff

Storing values as csv is very bad database design. Think about normalization of your tables — Jens, Apr 19 '17 at 09:41
Possible duplicate of [MySQL Join two tables with comma separated values](http://stackoverflow.com/questions/19101550/mysql-join-two-tables-with-comma-separated-values) — Shadow, Apr 19 '17 at 09:50
Or perhaps this is an even better duplicate: http://stackoverflow.com/questions/16923471/mysql-join-comma-separated-query — Shadow, Apr 19 '17 at 10:11

Blank · Accepted Answer · 2017-04-19T10:12:09.513

0

Try this:

SELECT GROUP_CONCAT(se.art ORDER BY FIND_IN_SET(se.id , ar.art_ids)) as art, ar.name as artist
FROM series AS se
JOIN artists AS ar ON FIND_IN_SET(se.id , ar.art_ids) > 0
GROUP BY ar.name

If you want to concat column in each group, GROUP_CONCAT can help you.

Check demo in Rextester.

edited Apr 19 '17 at 10:12

answered Apr 19 '17 at 09:46

Blank

12,308
1
14
32

its result is like this sculptor,sculptor,sculptor | john only with 1 row rest ommited – Faiq Ahmad Apr 19 '17 at 09:56
@FaiqAhmad Check demo. – Blank Apr 19 '17 at 10:12
here is my actual query where table size have two fields 1-id 2-name where my table products have id and size "SELECT group_concat(size.name ORDER BY FIND_IN_SET(`eshop_trans`.`products`.`size`, `eshop_trans`.`size`.`id`)) as size,`products`.`id` FROM `eshop_trans`.`products` join `eshop_trans`.`size` on FIND_IN_SET(`eshop_trans`.`products`.`size`, `eshop_trans`.`size`.`id`)>0 group by `size`.name " – Faiq Ahmad Apr 19 '17 at 10:35

MySQL innerjoin on an array field

1 Answers1