SQL & PL/SQL

Hi guys,

I am not sure why the left join is not working.. when I try to use the and condition out of the left join condition. The database is relational but the requirement is for reporting. I have 2 user inputs and I should get the query results as explained below..

Let me explain with the sample data.

Table 1: PRTYPE (P)
----------------------
TYPEID CLASSID
----------------------
T001 CLS001
T001 CLS002
T001 CLS003
T002 CLS002
T003 CLS001

Table 2: EMPLOYEE (E)
--------------------
EMPID NAME
--------------------
E001 Joe
E002 Mark
E003 Lucy

Table 3: DETAILS (D)
-----------------------------------
EMPID CLASSID STATUS
-----------------------------------
E001 CLS001 NEW
E001 CLS002 DONE
E002 CLS001 NEW
E002 CLS004 NEW

Report1:
Input: PRTYPE.TYPEID = T001, EMPID = E001
Output:

E.NAME E.EMPID P.TYPEID A.CLASSID D.STATUS
JOE EMP001 T001 CLS001 NEW
JOE EMP001 T001 CLS002 DONE
JOE EMP001 T001 CLS003 BLANK

Report2:
PRTYPE.TYPEID = T003, EMPID = E003

E.NAME E.EMPID P.TYPEID A.CLASSID D.STATUS
LUCY E003 T003 CLS001 BLANK
LUCY E003 T003 CLS004 BLANK

When I use and condition in left join itself, it works but when I take it to the end of the sql using where p.typeid= T001 and E.EMPID = E001 I am not getting the left join results... It looks like Oracle doesn't like the condition at the end. I hope I am clear on explaining. Please share your experience on how to get it working..

Thank you