Trying to get a result array of strings instead of ids

Question

Hi im trying to get the following results

Table1

ID | Name
 1 | Name1
 2 | Name2

Table2

ID | State | NameIDS
 1 | NY    | 1,2
 2 | ZA    | 1

I am trying to joun by th entire array so i get

ID | State | NameIDS
 1 | NY    | Name1,Name2
 2 | ZA    | Name1

trying to get this in a single query ive tried

Select Table2.ID,State,Table1.Name 
From Table2 
    JOIN Table1 ON FIND_IN_SET(Table1.ID, Table2.NameIDS) != 0

But this gives me

   ID | State | NameIDS
    1 | NY    | Name1
    1 | NY    | Name2
    2 | ZA    | Name1

Is it too late to fix the database design error that has a comma delimited list in a single field on a database row? — RiggsFolly, Jun 20 '19 at 08:41

score 0 · Accepted Answer · answered Jun 20 '19 at 11:02

Fix your data model! You should not be stored multiple ids in a string list. Here are some good reasons why:

Numbers should be stored as numbers, not strings.
Columns should contain only one value.
Databases are not very good or efficient at string functions.
Ids should be declared with proper, foreign key relationships.
Databases have this great structure for storing lists. It is called a table!

Sometimes, you are stuck with other peoples really, really, really, really, really bad data modeling decisions. If that is the case, you can do:

select t2.ID, t2.State,
       group_concat(t1.Name order by t1.id) as names
from Table2 t2 join
     Table1 t1
     on find_in_set(t1.ID, t2.NameIDS) <> 0
group by t2.ID, t2.State;

Trying to get a result array of strings instead of ids

1 Answers1