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
reference id
empCode
insCode
code
role
id | code | name | empCode | insCode | role |
1 | ? | John | E101 | I101 | ? |
select
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;