CODEWITHSUNDEEP
oracle-database
FenrisL
FenrisL

Reputation: 287

Oracle SQL - Query 2 tables based on their foreign keys

I have 2 tables and i want to make a single query and group them based on their foreign key. Here's an example :

Departments

DEPART_ID(PK) - DEPART_NAME

   1          - Accounting
   2          -    IT

Employees

EMP_ID(PK) - NAME - SURNAME - DEPART_ID(FK)

  1        - John   - John   -    1
  2        - George - George -    2

Managers

MANAG_ID(PK) - NAME    - SURNAME   - DEPART_ID(FK)
     1       - Nick    - Nick      -     1
     2       - Michael - Michael   -     2

And i want to get this output grouped by their department name.

Accounting      |       IT 
----------------|-------------------
John John       |   George George
Nick Nick       |   Michael Michael

I've tried but i can't make it to work. Whats the best way to do this?

Upvotes: 1

Views: 523

Answers (2)

APC
APC

Reputation: 146249

Here is one solution. The sub-query formats the name and generates a join key for employees within department. The main query splits the WITH output into sub-queries based on depart_id which are joined on the generated key; the full outer join allows for departments to have different numbers of people.

with emps as
    ( select depart_id, 
             name || ' ' || Surname  ename ,
             row_number() over (partition by depart_id order by emp_id) rn
      from employees)
select acct.ename as accounting
       , it.ename as IT
       , sal.ename as sales
       , hr.ename as hr
from (select * from emps where department_id = 1) acct
     full outer join (select * from emps where department_id = 2) it
     on acct.rn = it.rn 
     full outer join (select * from emps where department_id = 3)  sal
     on acct.rn = sal.rn 
     full outer join (select * from emps where department_id = 4)  hr
     on acct.rn = hr.rn 
order by acct.rn, it.rn, sal.rn, hr.rn
/

Upvotes: 0

Prescott Chartier
Prescott Chartier

Reputation: 1673

You can get what you want by using this:

select *  from
(select name || ' ' || Surname Accounting from employees where depart_id = 1),
(select name || ' ' || Surname  IT from employees where depart_id =2)
union
select * from
(select name || ' ' || Surname Accounting from managers where depart_id = 1),
(select name || ' ' || Surname  IT from managers where depart_id =2)

Upvotes: 1

Related Questions