CODEWITHSUNDEEP
sqloracle-databasejoin
gbvisconti
gbvisconti

Reputation: 412

Oracle - SQL Join 3 columns

I have 3 tables. One to keep data about Person, other to keep data about employee, and other to keep data about tasks the employee did.

Like this

Person 

    (id_person, name, ...)

Employee

(id_employee, id_person, ...)

Task

(id_task, id_employee, id_supervisor, id_supervisor_boss)

The columns id_employee, id_supervisor, id_supervisor_boss are all mapped to the Employee.id_employee

How can I get the name of those 3?

Doing something like

SELECT P.name 
FROM task T
JOIN Employee E on (T.id_employee = E.id_employee)
JOIN Person P on (E.id_person = P.id_person)
where T.id_task = 1;

will only bring the name of one (the guy who did the task), but I need the supervisor and supervisor_boss

Upvotes: 0

Views: 70

Answers (2)

Jonathan Wilson
Jonathan Wilson

Reputation: 61

you need to go to join to the employee and person tables three times (employee/supervisor/boss). Here's one solution:

           select e.name employee_name, e_s.name supervisor_name, e_sb.name supervisor_boss_name 
            from task t, 
                 employee e_e, employee e_s, employee e_sb, 
                 person e, person s, person sb
            where  t.id_employee = e_e.id_employee
            and e_e.person_id  e.person_id
            and t.id_supervisor = e_s.id_employee
            and e_s.person_id = s.person_id
            and t.id_supervisor_boss = e_sb.id_employee
            and e_sb.person_id = sb.person_id
            and t.id_task = 1;

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26333

You need to join the Employee and Person tables once for each type: Employee, Supervisor, and Supervisor Boss. This can be done by aliasing the tables:

SELECT
  EmpPerson.Name,
  SupPerson.Name,
  BossPerson.Name
FROM Task T
INNER JOIN Employee EmpEmp ON T.ID_Employee = EmpEmp.ID_Employee)
INNER JOIN Person EmpPerson ON EmpEmp.ID_Person = EmpPerson.ID_Person
INNER JOIN Employee SupEmp ON T.ID_Supervisor = SupEmp.ID_Employee
INNER JOIN Person SupPerson ON SupEmp.ID_Person = SupPerson.ID_Person
INNER JOIN Employee BossEmp ON T.ID_Supervisor_Boss = BossEmp.ID_Employee
INNER JOIN Person BossPerson ON BossEmp.ID_Person = BossPerson.ID_Person

Upvotes: 2

Related Questions