SQL: How to create a database view from tables with OneToMany Relationship?

There are three available tables and columns:

Core - EmpID, EmpName
       1, Yagga Boshu

External - ExternalEmpID, ExternalDeptName
           1 , Capegemini

Position - EmpID, Dept  P/S
           1, IT        Primary
           1, Finance   Secondary

I want to create view from in following format:

   Emp ID, EmpName, ExternalID, ExternalName, EmpPrimaryDept, EmpSecondaryDept
   1,     Yagga Boshu,  1 ,      CapeGemini  ,  IT,           FINANCE

1. EmpID and EmpName comes from Core Table.
2. ExternalID and ExternalName comes from External Table which is OneToOne with core table
3. EmpPrimaryDept and EmpSecondaryDept comes from Position Table which is OneToMany with core table

For 1) and 2) following query works:

select * from CORE ec, EXTERNAL ee
where ec.EMPLOYEEID = ee.ExternalEmpID

How to join Position table so that return comes in one row as described above?

• There are 20,000 Employees data in core.
• In question where you see 1 Emp ID those are linkage between two tables.
• I am using Oracle Database 12c Release 12.1.0.1.0 – 64bit Production

Answer

One way is to use subqueries in the select clause:

select
  c.empid, c.empname, e.externaldeptname,
  (select dept from position p where p.empid = c.empid and p.p_s = 'Primary') as dept1,
  (select dept from position p where p.empid = c.empid and p.p_s = 'Secondary') as dept2
from core c
left join external e on e.externalempid = c.empid
order by c.empid;

You can also move them to the from clause:

select
  c.empid, c.empname, e.externaldeptname, p.dept as dept1, s.dept as dept2
from core c
left join external e on e.externalempid = c.empid,
left join (select empid, dept from position where p_s = 'Primary') p on p.empid = c.empid
left join (select empid, dept from position where p_s = 'Secondary') s on s.empid = c.empid
order by c.empid;