2

Can someone help me in writing Oracle SQL Query for the following scenario:

  1. Company can have multiple departments.
  2. Each department can have multiple Employees.
  3. Employee need not have department assigned but company Id is must.

This is the output I am trying for:

enter image description here

This is the query I have tried So far:

SELECT C.id   Company_Id, 
       C.name Company_Name, 
       D.id   Department_Id, 
       D.name Department_Name, 
       E.id   Employee_Id, 
       E.name Employee_Name 
FROM   Company C 
       FULL OUTER JOIN Department D 
                    ON D.CompanyId = C.Id 
       FULL OUTER JOIN Employee E 
                    ON E.CompanyId = C.Id AND E.DepartmentId = D.Id
ORDER BY Company_Id,Department_Id,Employee_Id;

But it gives this output:

enter image description here

EDIT:

Sql fiddle : http://sqlfiddle.com/#!4/df238/3/0

javanoob
  • 6,070
  • 16
  • 65
  • 88
  • 1
    It is good that you added sqlfiddle, although I don't know when you added it. But, it is always better to post sample data in text format rather than images. In many workplace computers, the images may not open due to restrictions and it is hard to recreate the scenario with images. – Kaushik Nayak Feb 24 '18 at 05:07

1 Answers1

3

The trick is to convert ON E.CompanyId = C.Id AND E.DepartmentId = D.Id join condition to
ON E.CompanyId = C.Id AND ( E.DepartmentId = D.Id OR E.DepartmentId is null )

SELECT C.id   Company_Id, 
       C.name Company_Name, 
       D.id   Department_Id, 
       D.name Department_Name, 
       E.id   Employee_Id, 
       E.name Employee_Name 
FROM   Company C 
       FULL OUTER JOIN Department D 
                    ON D.CompanyId = C.Id 
       FULL OUTER JOIN Employee E 
                    ON E.CompanyId = C.Id AND ( E.DepartmentId = D.Id OR E.DepartmentId is null )
ORDER BY Company_Id,Department_Id,Employee_Id;

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55