I'm stuck on creating MySQL queries. I have two tables, which are user and factory. The data and structure are as below:
- table user
- table factory
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?
CodePudding user response:
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