0

I have two 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
INNER JOIN artists AS ar ON (se.id IN (ar.art_ids))

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 | jack
sculptor | jill
sculptor | jeff
painter  | jack
painter  | jill
writer   | jeff

Normally I would do this with a third table with the links pe.id<->se.id. But another table is quite complicated to maintain in my framework.

Improve this question
2
  • I would recommend that you do normalize out that field, it is the reason for your results and will constantly cause you more hassle, than not adopting good design within the DB Commented Nov 9, 2011 at 23:29
  • @Andrew I see that and normally I would. Thank you. Since both tables are not going to change much and I artists is updated by users via a standard Joomla form (where I can simply use a multi-select list to create the field) I prefer not to change that. Commented Nov 10, 2011 at 0:50

1 Answer 1

Reset to default
2

As mentioned above the best option is to fix your table structure, its best to do it now while you have the chance. As the data grows it will start causing a lot of headaches. However if you know what you are doing, i think this will get you what you want in the short term:

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I did search a lot, but now when I search for FIND_IN_SET there are a few similar questions.... I apologise. One thing for anyone who tries this: you cannot have spaces in the string. And with a lot of data it gets slow because mysql cannot use indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.