Select returning values from an Array

Question

I would like to know if it is possible to select a field with an array in the format 1|3.

I have a USER table and another COMPANY, in the USER table I have a field where I record the companies that this user has a relationship with, thus;

USERS TABLE

ID - Name - Companies

01 - Joe - 1|3

COMPANIES TABLE

ID - Name

01 - Bradesco

03 - Itau

I would like the result to be this;

ID - Name - Companies

01 - Zé - Bradesco, Itaú

1. You may use a table `user_companies` where you have `user_id` and `company_id`. Each user can have mutliple companies. So (user_id|company_id) 1|2 ; 1|5 ... . 2. In your example you could select ```WHERE `companies` LIKE '%3%'``` but this will make problems with searching for "1" in "11". You also could use REGEX, but ... you see - this format just causing problems. — cottton, May 05 '22 at 20:40
If you used comma as the delimiter instead of `|`, you could use `FIND_IN_SET()`. — Barmar, May 05 '22 at 20:47
See [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Barmar, May 05 '22 at 20:47
But I need the result to be the names of the companies separated by '," as in the example (Bradesco, Itaú) — Leandro Diniz, May 05 '22 at 20:59
Store it in separately in its place - retrieve and then use CONCAT() to format it? — almaruf, May 06 '22 at 15:20

score 0 · Answer 1 · answered May 05 '22 at 22:54

The SQLish way to express this many::many users::companies relationship is with a so-called join table.

If you design your database around multiple values in columns separated by delimiters, you will be sorry. You'll have trouble maintaining it, and using it will be, at best, slow.

users

user_id   name
 1        Joe
 2        Ollie
 3        Leandro

companies

company_id   name
   01        Bradesco
   03        Itau

users_companies (your table, containing a row for each user-to-company association)

 user_id    company_id
   1          1           These two rows implement **1|3**
   1          3
   3          3           This row implements **3**

To create an association between a user and a company, you insert a row into this table. To remove that association you delete that row. To display the data you do this.

SELECT users.name, GROUP_CONCAT(companies.name) companies
  FROM users
  JOIN users_companies ON users.user_id = users_companies.user_id
  JOIN companies ON users_companies.company_id = companies.company_id
 GROUP BY users.name

Here is an example. It generates this.

name     companies    
Joe      Itau|Bradesco
Leandro  Itau

The problem is that the system has existed for years and I need to show this information on the client's screen. Changing that now would not be feasible. Is there any way to do this? if yes could you show me how or the way? — Leandro Diniz, May 06 '22 at 13:38

Select returning values from an Array

1 Answers1