hooman182 hooman182 - 11 days ago 6
MySQL Question

Show two name from one table different id

I have two tables

tb_masteropsi

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


tb_employee

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

select
a.*,
b.*
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?

Answer

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

SELECT
    te.`id`,
    te.`name`,
    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