Displaying Records Joining

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Displaying Records Joining

Postby ponic » Wed Feb 22, 2012 11:53 am

Hello Tim

I have two tables containing data
1) master table with employees work schedules
Code: Select all
create table EMP_WORK_SCH
(EMP_NUMBER varchar2(25),
START_DATE date,
END_DATE date);

SET DEFINE OFF;
Insert into EMP_WORK_SCH
   (EMP_NUMBER, START_DATE, END_DATE)
 Values
   ('JACKY', TO_DATE('08/08/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/13/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EMP_WORK_SCH
   (EMP_NUMBER, START_DATE, END_DATE)
 Values
   ('SAM', TO_DATE('08/08/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/09/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into EMP_WORK_SCH
   (EMP_NUMBER, START_DATE, END_DATE)
 Values
   ('JACKY', TO_DATE('08/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/02/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


2) Details table back_emp

Code: Select all
create table BACK_EMP
(EMP_NUMBER varchar2(25),
BACKUP_EMP_NUMBER varchar2(25));

SET DEFINE OFF;
Insert into BACK_EMP
   (EMP_NUMBER, BACKUP_EMP_NUMBER)
 Values
   ('JACKY', 'SAM');
Insert into BACK_EMP
   (EMP_NUMBER, BACKUP_EMP_NUMBER)
 Values
   ('SAM', 'TOM');
Insert into BACK_EMP
   (EMP_NUMBER, BACKUP_EMP_NUMBER)
 Values
   ('DANTE', 'EDMUND');
COMMIT;



What I would like to achieve is display like
Code: Select all
EMP_NUMBER     START_DATE     END_DATE                BACKUP_EMP         START_DATE       END_DATE
JACKY          08/08/2005     08/13/2005              SAM                08/08/2005       08/09/2005
SAM            08/04/2005     08/05/2005              TOM                08/01/2005       08/02/2005


How can I get this desired result?

Thanks
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Displaying Records Joining

Postby Tim... » Wed Feb 22, 2012 2:25 pm

Hi.

Your question was really well laid out, but there were a lot of mistakes in your scripts. Extra braces in table definitions and incorrect column names in the INSERTs compared to the table definition. I've corrected them in the post in case anyone else is looking at it.

The output you have given is impossible for the test data you have supplied. I believe the third insert into EMP_WORK_SCH has not been supplied correctly. I believe it should have read TOM, not JACKY:

Code: Select all
Insert into EMP_WORK_SCH
   (EMP_NUMBER, START_DATE, END_DATE)
 Values
   ('TOM', TO_DATE('08/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/02/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


If this is the case, the solution is a pretty simple join.

Code: Select all
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY';

SELECT ews1.emp_number,
       ews1.start_date,
       ews1.end_date,
       ews2.emp_number AS backup_emp ,
       ews2.start_date AS backup_start_date,
       ews2.end_date AS backup_end_date
FROM   back_emp be
       JOIN emp_work_sch ews1 ON be.emp_number = ews1.emp_number
       JOIN emp_work_sch ews2 ON be.backup_emp_number = ews2.emp_number;

EMP_NUMBER                START_DATE END_DATE   BACKUP_EMP                BACKUP_STA BACKUP_END
------------------------- ---------- ---------- ------------------------- ---------- ----------
JACKY                     08/08/2005 13/08/2005 SAM                       08/08/2005 09/08/2005
SAM                       08/08/2005 09/08/2005 TOM                       01/08/2005 02/08/2005

2 rows selected.

SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Displaying Records Joining

Postby ponic » Thu Feb 23, 2012 7:39 am

Sorry for providing incorrect data.
Thanks again Tim, it worked.

Regards
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Displaying Records Joining

Postby Tim... » Thu Feb 23, 2012 8:53 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest