Joining table into 4 columns and show another fields

I have a new problem, as information from my question before, I already successfully show 4 column from joining another table, but I got another problem to show another column, for more detail :

Table personal (tbl_personal)

+---+-----+------------+
|ID |Name |Skill       |
+---+-----+------------+
| 1 | Nia | Accountant |
| 2 | Tia | Banking    |
| 3 | Ria | Technicall |
| 4 | Dia | Admin      |
+---+-----+------------+

Table master (tbl_master)

+---+---------+---------+-----------+--------+----+
|ID |Employee1|Employee2|Departement|Division|Note|
+---+---------+---------+-----------+--------+----+
| 1 | 1       | 2       | MSO       | MWS    | etc|
| 2 | 2       | 1       | DSO       | SE     | etc|
| 3 | 3       | 4       | OSO       | GA     | etc|
+---+---------+---------+-----------+--------+----+

I want to show into web page from tbl_master above in codeigniter like this :

+---+-------+-----------+-------+-----------+-----------+--------+----+
|ID |Name 1 |Skill 1    |Name 2 |Skill 2    |Departement|Division|Note|
+---+-------+-----------+-------+-----------+-----------+--------+----+
| 1 | Nia   | Accountant| Tia   | Banking   | MSO       | MWS    | etc|
| 2 | Tia   | Banking   | Nia   | Accountant| DSO       | SE     | etc|
| 3 | Ria   | Technicall| Dia   | Admin     | OSO       | GA     | etc|
+---+---------+---------+-------+-----------+-----------+--------+----+

I already got solution to show Name 1, Skill 1 until Skill 2 with this code :

select tp1.name name1,tp1.skill skill1,tp2.name name2,tp2.skill skill2 
from tbl_master tm
join tbl_personal tp1
on tm.Employee1 =tp1.ID
join tbl_personal tp2
on tm.Employee2 =tp2.ID

but I have another problem to show departemen, Division, and Note columns, if there any advice, please, thanks…

Answer

You just need to change your select and add those columns:

select tp1.name name1,tp1.skill skill1,tp2.name name2,tp2.skill skill2,tm.Departement, tm.Division, tm.Note
from tbl_master tm
join tbl_personal tp1
on tm.Employee1 =tp1.ID
join tbl_personal tp2
on tm.Employee2 =tp2.ID

Advertisement

Answer