0

I'm stuck in joining two tables. My case is very unique. I have two tables as below:

Table user
+---------+----------+--------------+
|user_id  |   fname  |  factory_ids |
+---------+----------+--------------+
|   1     |    Andrew|   1,2,3      |
|   2     |  Roberts |   2,2        |
+---------+----------+--------------+

Table factory
+------------+----------+
|factory_id  |   fname  |
+------------+----------+
|   1        |    F1    |
|   2        |    F2    |
|   3        |    F3    |
|   4        |    F4    |
+------------+----------+

Now, if I want to select all user data and display their factory id and factory name, how to display the factory_name, if the factory_ids are 1,2,3? I want it to display factory_name, F1,F2,F3?

Anyone can help?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Ratu Batu Hamlo Koto
  • 15
  • 5
  • https://stackoverflow.com/q/2923809/251311 – zerkms Feb 10 '23 at 00:48
  • Update your question with the results of these, `SHOW CREATE TABLE user;` and `SELECT version();` – FanoFN Feb 10 '23 at 00:50
  • Those comma-separated lists make this task difficult, unpleasant, and brittle, sorry to say. – O. Jones Feb 10 '23 at 01:20
  • @O.Jones, then? any idea? – Ratu Batu Hamlo Koto Feb 10 '23 at 01:22
  • 1
    https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Paul T. Feb 10 '23 at 01:53
  • 1
    `.. WHERE FIND_IN_SET(factory.factory_id, user.factory_ids) ..` *My case is very unique.* No, this is very common bad-practice example. – Akina Feb 10 '23 at 04:40
  • is `user.factory_ids` a set or a varchar? – gildux Feb 10 '23 at 06:12
  • Take a look at this. You'll need a massively verbose UNION of queries with [SUBSTRING_INDEX()](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index) calls in each one. https://stackoverflow.com/questions/47144201/mysql-finding-the-most-frequently-used-words-in-a-comma-delimited-field/47144797#47144797 – O. Jones Feb 10 '23 at 14:08

0 Answers0