1

I have two tables:

EMPLOYEE

Name | DepartmentId | Address | Phone number
--------------------------------------------
Tom  | [a1, b2] | 51 Rue Fort  | 123456
Ron  | [a1]    | San Picafort | 234566
Matt | [b2]    | Frank Road   | 523422

DEPARTMENT

Id | Dept Name | Number of Employees | Location 
------------------------------------------
a1  | HR | 500 | Zambia
b2  | IT | 300 | Tanzania

I need to join two tables on DepartmentId, but the problem is DepartmentId in EMPLOYEE table is an array. I cannot run join on it. Like:

select *
from EMPLOYEE,
     DEPARTMENT
where EMPLOYEE.DepartmentId = DEPARTMENT.id;

Which will give output like:

Name | DepartmentId | Address | Phone number | Dept Name | Number of Employees | Location 
---------------------------------------------------------------------
Tom | a1 | 51 Rue Fort | 123456 | HR | 500 | Zambia
Tom | b2 | 51 Rue Fort | 123456 | IT | 300 | Tanzania
Ron | a1 | San Picafort | 234566 | HR | 500 | Zambia
Matt | b2 | Frank Road | 523422 | IT | 300 | Tanzania

How can I make EMPLOYEE table atomic using a query and then run a join query?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Bharthan
  • 1,458
  • 2
  • 17
  • 29
  • 3
    Never ever store data as comma separated items. Your DepartmentId column will only cause you lots of trouble. – jarlh May 08 '19 at 13:10
  • I got data from a NOSQL DB like this, I can't help it. Data is not normalized there. – Bharthan May 08 '19 at 13:10
  • 1
    MySQL does not support arrays. You have a string, formatted as a JSON array. That is not the same thing. You should be using a junction table instead of ids formatted as a string. You should put your efforts into fixing the data model. – Gordon Linoff May 08 '19 at 13:11

3 Answers3

1

Arrays are difficult to deal with and I am unsure whether @mkRabbani has the faster solution with LIKE but an alternative that may be better suited is a join using FIND_IN_SET(). Both are extremely inefficient, but the query would look like this: 

SELECT *
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D
    ON FIND_IN_SET(D.Id, E.DepartmentId)
mtr.web
  • 1,505
  • 1
  • 13
  • 19
1

I think a cleaner approach could be having an extra table representing the many-to-many relationship between EMPLOYEE and DEPARTMENT. Let's say EMPLOYEE_DEPARTMENT. With one row for every relationship between an employer and a department. Of course, you can also add an ID to the employee table. I think all queries will be different but with a way better performance.

The tables will be like this:

EMPLOYEE

Id | Name | Address     | Phone number
-----------------------------------
1 | Tom  | 51 Rue Fort  | 123456
2 | Ron  | San Picafort | 234566
3 | Matt | Frank Road   | 523422

DEPARTMENT

Id | DeptName | Number of Employees | Location 
-----------------------------------------------
a1  | HR      | 500                 | Zambia
b2  | IT      | 300                 | Tanzania

EMPLOYEE_DEPARTMENT

Emp | Dept 
-----------
1  | a1
1  | b2
2  | a1 
3  | b2

The query you're asking about could be:

Select E.*, D.Id from EMPLOYEE as E left join EMPLOYEE_DEPARTMENT as ED on E.Id=ED.Emp left join DEPARTMENT as D on ED.Dept=D.Id;

Producing output:

Id | Name | Address     | Phone number | Dept Name | Number of Employees | Location | DepartmentId  
---------------------------------------------------------------------------------------------------
1  | Tom   | 51 Rue Fort | 123456       | HR        | 500                 | Zambia   | a1  
1  | Tom   | 51 Rue Fort | 123456       | IT        | 300                 | Tanzania | b2 
2  | Ron   | San Picafort| 234566       | HR        | 500                 | Zambia   | a1
3  | Matt  | Frank Road  | 523422       | IT        | 300                 | Tanzania | b2

Also on the output you can change DepartmentId with other columns of DEPARTMENT table.

nilsandrey
  • 1,030
  • 11
  • 28
0

Create the inner join with LIKE operator

SELECT E.NAME,
E.DepartmentId,
E.Address,
E.[Phone number],
D.[Dept Name],
D.[Number of Employees],
D.Location 
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D
    ON CAST(E.DepartmentId AS VARCHER(MAX)) LIKE '%' + CAST(D.id AS VARCHER(MAX))+ '%'
mkRabbani
  • 16,295
  • 2
  • 15
  • 24