INNER JOIN on One Table but different Parameter

Question

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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!

INNER JOIN on One Table but different Parameter

Sign up