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ú

Answer 1

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