Marksmanship Marksmanship - 2 years ago 128
MySQL Question

INNER JOIN on One Table but different Parameter

I am trying to get different values on rows of one table, and I can't think of how to that.

Lets say for example:

table1
id | name | empCode | insCode
1 John | E101 | I101


table2
id | code | name | role
1 | E101 | Mich | 2
2 | I101 | Bran | 3


table1 has
reference id
of table2, and
empCode
and
insCode
in table1 dependes on
code
and
role
in table2.

What I want is like this:

id | code | name | empCode | insCode | role |
1 | ? | John | E101 | I101 | ? |


I honestly don't know if its possible to retrieve multiple rows in same table in one
select
query.
If its not, what other possible solutions can I go with?

Answer Source

There is problem in your table design but you can get as your expected with below query:

select 
result1.id, 
result1.code,
result1.name, 
result1.empCode, 
result1.insCode, 
result1.role as role_of_empCode,
result2.role as role_of_insCode
from (
  SELECT tbl1.id, 
  tbl2.code,
  tbl1.name, 
  tbl1.empCode, 
  tbl1.insCode, 
  tbl2.role
  FROM `table2` tbl2
  INNER JOIN `table1` tbl1 
  on tbl2.code = tbl1.empCode
) as result1, 
`table2` result2
where result1.insCode = result2.code
ORDER BY result1.id;

You can check in this link!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download