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
– Jens, Commented Apr 19, 2017 at 9:41
Possible duplicate of MySQL Join two tables with comma separated values — Shadow
– Shadow, Commented Apr 19, 2017 at 9:50
Or perhaps this is an even better duplicate: stackoverflow.com/questions/16923471/… — Shadow
– Shadow, Commented Apr 19, 2017 at 10:11

Blank · Accepted Answer · 2017-04-19 10:12:09Z

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, 2017 at 10:12

answered Apr 19, 2017 at 9:46

Blank

12.4k1 gold badge16 silver badges34 bronze badges

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

3 Comments

Faiq Ahmad Over a year ago

its result is like this sculptor,sculptor,sculptor | john only with 1 row rest ommited

Blank Over a year ago

@FaiqAhmad Check demo.

Faiq Ahmad Over a year ago

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 "

Collectives™ on Stack Overflow

MySQL innerjoin on an array field

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related