• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

How to create the outer joins for the sample tables - ORACEL 11G

How to formulate the select for the three sample tables below
To bring back all titles even though there are is no matching data in the Person_classes table
And bring back only employees whose employee_id is in the person_classes table


Titles_Table
Title_ID    Title
 1             S-QUAL
 2             M211-TEST
 3             957-1-LOTUS
 4             KITS



Employees  
  Emp_Id     Pay_Grade     Position           Name
   101         A1                  SECRETARY       DOE, JOHN
   102         A3                  ANALYST           KELLY, SUE
   103         P1                  PROGRAMMER   MACY, LAUREN


Persons_Classes
Per_Class_Id  Emp_Id    Title_ID     Class_Status   ExpirationDate
  1                    101          1             ACTIVE             01/12/2013
  2                    102          4             ACTIVE             01/12/2014



RESULTS
 TITLE_ID TITLES     EMP_ID   PAY_GRADE    CLASS_STATUS       EXPIRATION_DATE
   1      S-QUAL          101          A1                 ACTIVE                             01/12/2013
   4      KITS               102          A3                ACTIVE                              01/12/2014
   2      M211-TEST
   3      957-1-LOTUS
   
   
0
cookiejar
Asked:
cookiejar
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
try

select t.*, p.*, e.*
from Titles_Table t
left join Persons_Classes p on p.Title_ID=t.Title_ID
left join Employees e on e.Emp_Id=p.Emp_Id
0
 
Swadhin RaySenior Technical Engineer Commented:
The notation for an outer join is the (+) notation, please find code as below:
CREATE TABLE Titles_Table (Title_ID NUMBER ,Title varchar2(20) );

begin
INSERT INTO Titles_Table values(1,'S-QUAL');
INSERT INTO Titles_Table values(2,'M211-TEST');
INSERT INTO Titles_Table values(3,'957-1-LOTUS');
INSERT INTO Titles_Table values(4,'KITS');
commit;
end;

CREATE TABLE Employees (Emp_Id NUMBER ,Pay_Grade varchar2(3),Position varchar2(30), Name varchar2(40)  ) ;

begin 
INSERT INTO Employees VALUES(101,'A1','SECRETARY','DOE, JOHN');
INSERT INTO Employees VALUES(102,'A2','ANALYST','KELLY, SUE');
INSERT INTO Employees VALUES(103,'A3','PROGRAMMER','MACY, LAUREN');
commit;
end;

CREATE TABLE Persons_Classes (Per_Class_Id NUMBER ,Emp_Id NUMBER ,Title_ID NUMBER ,Class_Status varchar2(30) ,ExpirationDate DATE );

begin
INSERT INTO Persons_Classes values(1,101,1,'ACTIVE',to_date('2013/12/01', 'yyyy/mm/dd')) ;
INSERT INTO Persons_Classes values(2,102,4,'ACTIVE',to_date('2014/12/01', 'yyyy/mm/dd'));
commit;
end;



SQL> select t.*, p.*, e.*
from Titles_Table t ,Persons_Classes p,Employees e
where t.Title_ID =p.Title_ID(+)
and p.Emp_Id = e.Emp_Id(+);

Open in new window

0
 
Swadhin RaySenior Technical Engineer Commented:
Another way to write it as :


SELECT t.*,
  p.*,
  e.*
FROM Titles_Table t
LEFT OUTER JOIN Persons_Classes p
ON p.Title_ID=t.Title_ID
LEFT OUTER JOIN Employees e
ON e.Emp_Id=p.Emp_Id;


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now