hooman182 hooman182 - 4 months ago 44
MySQL Question

Show two name from one table different id

I have two tables


id, type, name
1, Job, Painter
2, Job, Singer
3, Use, Kanvas
4, Use, Guitar
5, Use, Microphone


id, name, job, use
1, Jhon, 1, 3
2, Mark, 2, 4
3, Tom, 2, 5

I'm using this query that obviously doesn't work

from tb_employee a
join tb_masteropsi b
on a.job = b.id and a.use = b.id;

My goal is to get following result

id, name, job, use
1, Jhon, Painter, Kanvas
2, Mark, Singer, Guitar
3, Tom, Singer, Microphone

How can I fix it?


You have to join tb_employee table twice to get two different rows from that table (once for 'job' and once for 'use' columns of tb_employee table).

So you need something like this to achieve expected result

    tm1.`name` as 'job',
    tm2.`name` as 'use'
FROM `tb_employee` te
    LEFT JOIN `tb_masteropsi` tm1
    ON tm1.`id`=te.`job`
    LEFT JOIN `tb_masteropsi` tm2
    ON tm2.`id`=te.`use`

Note: Please, consider splitting tb_masteropsi table into two separate tables - one for "Job" and one for "Use".

Navicat test preview:

enter image description here