Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Query
Outer Join Query [message #194653] Sat, 23 September 2006 10:50 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
I am somewhat new to Oracle SQL, PL/SQL. I am quite familiar with SQL Server T-SQL.
I wanted to accomplish the following, below are the DDLs and DMLs

CREATE TABLE EMP_MASTER(EMP_ID NUMBER, NAME VARCHAR2(100));

CREATE TABLE TIME_BOOKED(EMP_ID NUMBER, TASK_ID NUMBER, TIME_DATE DATE);

CREATE TABLE TASK_MASTER(TASK_ID NUMBER, TASK_NAME VARCHAR2(100));

CREATE TABLE EMP_RATE(EMP_ID NUMBER, TASK_ID NUMBER, RATE NUMBER, FROM_DATE DATE, TO_DATE DATE);


INSERT INTO EMP_MASTER VALUES(1000, 'EMPLOYEE_1000');
INSERT INTO EMP_MASTER VALUES(2000, 'EMPLOYEE_2000');
INSERT INTO EMP_MASTER VALUES(3000, 'EMPLOYEE_3000');
INSERT INTO EMP_MASTER VALUES(4000, 'EMPLOYEE_4000');

INSERT INTO TIME_BOOKED VALUES(1000, 10, '01-MAY-06', 9);
INSERT INTO TIME_BOOKED VALUES(1000, 10, '02-MAY-06', 9);
INSERT INTO TIME_BOOKED VALUES(2000, 20, '01-MAY-06', 7);
INSERT INTO TIME_BOOKED VALUES(2000, 20, '02-MAY-06', 9);

INSERT INTO TASK_MASTER VALUES(10, 'TASK10');
INSERT INTO TASK_MASTER VALUES(20, 'TASK20');

INSERT INTO EMP_RATE VALUES(1000, 10, 23, '01-JAN-06', '31-DEC-08');

COMMIT;


Based on this I want a query which shows the EMP_ID, NAME, TIME_DATE, HOURS, TASK_NAME, RATE

It should show all the rows from EMP_MASTER, TIME_BOOKED, TASK_MASTER irrespective of matching rows from EMP_RATE

EMP_ID    NAME         TIME_DATE    HOURS    TASK_NAME    RATE
1000     EMPLOYEE_1000  01-MAY-06    9        TASK10       23
1000     EMPLOYEE_1000  02-MAY-06    9        TASK10       23
2000     EMPLOYEE_2000  01-MAY-06    7        TASK20       NA
2000     EMPLOYEE_2000  01-MAY-06    9        TASK20       NA


I am able to write the below inner join query which shows only 2 rows.

SELECT E.EMP_ID, E.NAME, T.TIME_DATE, T.HOURS, TM.TASK_NAME, ER.RATE
FROM EMP_MASTER E,
TIME_BOOKED T,
TASK_MASTER TM,
EMP_RATE ER
WHERE 
E.EMP_ID=T.EMP_ID
AND T.TASK_ID=TM.TASK_ID
AND E.EMP_ID=ER.EMP_ID
AND T.TASK_ID=ER.TASK_ID
AND T.TIME_DATE BETWEEN ER.FROM_DATE AND ER.TO_DATE


Thank you in advance.

Anand
Re: Outer Join Query [message #194656 is a reply to message #194653] Sat, 23 September 2006 11:12 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
SELECT E.EMP_ID, E.NAME, T.TIME_DATE, T.HOURS, TM.TASK_NAME, ER.RATE
FROM EMP_MASTER E,
TIME_BOOKED T,
TASK_MASTER TM,
EMP_RATE ER
WHERE ER.EMP_ID=T.EMP_ID(+)
AND T.TASK_ID=TM.TASK_ID(+)
AND E.EMP_ID=ER.EMP_ID(+)
AND T.TASK_ID(+) =ER.TASK_ID
AND T.TIME_DATE(+) BETWEEN ER.FROM_DATE AND ER.TO_DATE


the rules of outer-joins dictate that if B is outer-joined to A, then any subsequent joins to B must be outer-joins as well. so outer join ER to E, T to ER, and finally TM to T. and because a table can only be outer-joined to one table (or in-line view), and changed T.EMP_ID from joining to E to ER.
Re: Outer Join Query [message #194686 is a reply to message #194656] Sun, 24 September 2006 01:13 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
shoblock wrote on Sun, 24 September 2006 02:12

... and because a table can only be outer-joined to one table (or in-line view)

Unless, of course, you update to the 9i+ ANSI join syntax.

Ross Leishman
Previous Topic: Blobs inserting/viewing
Next Topic: How to Grant CONNECT only privleges to user?
Goto Forum:
  


Current Time: Sun Dec 04 00:46:00 CST 2016

Total time taken to generate the page: 0.18787 seconds