Displaying Records Joining

[ponic]

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

[Tim...]

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...

[ponic]

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

Regards

[Tim...]