CODEWITHSUNDEEP
mysqldatabaseinner-join
user4025957
user4025957

Reputation:

INNER JOIN MULTIPLE COLUMN WITH ONE TABLE MYSQL

I have 2 different table, one with the category ID and name and one with my employee information. An employee can have more category.

Example of table:

Category table:

IDCAT | Name
----------
 1 | Mechanic
 2 ! Office
 3 | Generic Mechanic

Employee table:

ID | Name              | cat1 | cat2 | cat3
--------------------------------------------
 1 | Mechanic          |  1   |   2  |
 2 ! Office            |  1   |   3  |   
 3 | Generic Mechanic  |  1   |   2  |   3

I'd like to print a table like this with MySQL so I can print it in my PHP script:

ID | Name              | cat1        | cat2                | cat3
--------------------------------------------------------------------------------
 1 | Mechanic          |  Mechanic   |  Office             |
 2 ! Office            |  Mechanic   |   Generic Mechanic  |   
 3 | Generic Mechanic  |  Mechanic   |  Office             |   Generic Mechanic

I tried whit this query but id only print the first category name

SELECT * FROM 'Employee' INNER JOIN category on Employee.cat1 = category.idcat

Can I do something to have my result or should I change my table? Thanks

Upvotes: 0

Views: 1960

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You should normalize your table Employee avoing multiple columns for category eg:

Category table:

IDCAT | Name
----------
 1 | Mechanic
 2 ! Office
 3 | Generic Mechanic
Employee table:

ID | Name              
----------------------
 1 | Emp1 Mechanic     
 2 ! Office            
 3 | Generic Mechanic 



Employee_category
id | id_emp | id_cat
-------------------------
1  ! 1      | 1
2  | 1      | 2
3  | 2      | 1
4  | 2      | 3
5  ! 3      | 1
6  | 3      | 2
7  | 3      ! 3

based on this schema you coudl obtain all categories related to an employee in one row using group_concat

  select e.name, group_concat(c.name)
  from employee e
  inner join Employee_category ec on e.id = ec.id_emp
  inner join Caetgory c on c.idcat = ec.id_cat
  group by e.name

Upvotes: 1

flip
flip

Reputation: 555

SELECT
    e.ID
    ,e.Name
    ,c1.Name
    ,c2.Name
    ,c3.Name
FROM
    Employee AS e
    LEFT JOIN Category as c1 ON e.cat1 = c1.ID
    LEFT JOIN Category AS c2 ON e.cat2 = c2.ID
    LEFT JOIN Category AS c3 ON e.cat3 = c3.ID

Note that your Employee table is structured poorly. Normally you would have a separate table to hold EmployeeID + CategoryID and add a foreign keys.

A many to many tutorial can be found here and should give you a better idea of how to structure your tables better.

Upvotes: 2

Related Questions