MYSQL: How to display data if foreign key is implode (',')

Question

I'm stuck on creating MySQL queries. I have two tables, which are user and factory. The data and structure are as below:

1. table user

enter image description here

2. table factory

enter image description here

From table user, u can see example, id = 1 got factoryID = 2,3. At table factory, id = 2 is F1 and id = 3 is F2.

Now, how i want to join the table, and display the data like example,

user.id = 1

user.name = Amira

user.factoryID = 2,3

factory.factoryName = F1,F2

Can anyone know how to write the query?

Answer 1

I suggest fixing your table design. Using FIND_IN_SET can do the trick, but you will be facing some performance issues, especially for larger data.

As per the question you could use:

select user.id,name,factoryID,group_concat(factoryName) as factoryName
from user  
inner join factory  ON FIND_IN_SET(factory.id,user.factoryID)
group by user.id,name,factoryID;

Result:

id    name    factoryID   factoryName
1    Armira      2,3         F1,F2
2    Balqis      4,5         F3,F4

Demo