INNER JOIN MULTIPLE COLUMN WITH ONE TABLE MYSQL

Question

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

Answer 1

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.