0

I have a table of values like:

id | genre
-------------
1  | Puzzle
2  | RPG
3  | Action
4  | Fighter
5  | Adventure

And in another table, I have a string of comma delimited Ids that match the above table, like:

'1, 3, 5'

And I want my query to return this field as:

'Puzzle, Action, Adventure'

Also, maybe is there a smarter way to approach this problem than storing this string of ids?

Garmy
  • 1
  • You only want to use a query, or is ok to work in another language? – Gerardo Jaramillo Dec 19 '16 at 22:49
  • 2
    this is what http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Dec 19 '16 at 22:50
  • Your “another table” is broken. Put each ID in a separate row, then you can join them. – Dour High Arch Dec 19 '16 at 22:51
  • This is a good read, e4c5.. I don't see any suggestions of the right way to do it though. – Garmy Dec 19 '16 at 22:56
  • Dour, when you say to put each Id in a separate row, do you mean its own column? like a isAction, isFighter, etc? I don't understand – Garmy Dec 19 '16 at 22:59
  • post your existing tables using `show create table` and might make a suggestion. Please also mention your favourite programming language – e4c5 Dec 19 '16 at 23:02
  • I haven't created the Genres table yet. Right now, my 'games' table just has a column for 'genres' and it's like 'Puzzle, Action, Adventure' – Garmy Dec 19 '16 at 23:06
  • But the user selects those with a checkbox system. Before I did much work to normalize this pattern, I was exploring options. I am using nodeJS btw – Garmy Dec 19 '16 at 23:06
  • 1
    why don't you completely edit this question, and post alll that. You speak of a Generes table but it isn't clear which of the two tables that is – e4c5 Dec 19 '16 at 23:07
  • @e4c5 Thank you for the book recommendation "SQL Antipatterns" - Bill Karwin; I've taken over a part of software with comma delimited ids in a database table - my first contact with a MySQL, didn't know that this was a common pitfall. I took the wrong approach.. Thanks Tom. – Tom Kuschel Jan 22 '17 at 10:19
  • glad the comments were usefull @TomKuschel – e4c5 Jan 22 '17 at 15:05

0 Answers0