HELP! Outer Join doesn't work when where clause criteria are added. 3

[CrystalVisualBOracle]

Hello all. I rarely have to use outer joins so this may be something simple I am overlooking.

Below is a query with two tables. There may be records in the LICENSES that don't have corresponding records in USERS so I created an outer join to get those records. The problem is that if there are records in USERS they must fulfill certain date criteria. When I add the two lines (in bold) below the query is no longer an outer join and will only returns records found in both tables. Without the two lines in bold it returns the records correctly. Does anyone know why this is happening?


SELECT A.ID, A.PUBLICATION_NAME, B.USERID, B.USERNAME
FROM LICENSES A, USERS B
WHERE A.ID = B.ID (+)
AND A.ID = 215
AND B.USER_STARTED <= '01-OCT-2001'
AND B.USER_EXPIRED >= '01-OCT-2001'


Thanks in Advance,
CrytalVisualBOracle

 

[stefanhei]

Hi.
Your outer join will work if you replace your users-table with a view that delivers only the data you need, like this:

SELECT A.ID, A.PUBLICATION_NAME, B.USERID, B.USERNAME
FROM LICENSES A, (SELECT * FROM USERS WHERE USER_STARTED <= TO_DATE('01-OCT-2001','DD-MON-YYYY') AND USER_EXPIRED >= TO_DATE('01-OCT-2001','DD-MON-YYYY')) B
WHERE A.ID = B.ID (+)
AND A.ID = 215

Performance of course will be horrible if the tables are big. If the USER_STARTED and _EXPIRED are VARCHAR2 convert them with TO_DATE too,to compare the dates correctly.

Stefan

 

[sem]

SELECT A.ID, A.PUBLICATION_NAME, B.USERID, B.USERNAME
FROM LICENSES A, USERS B
WHERE A.ID = B.ID (+)
AND A.ID = 215
AND B.USER_STARTED(+) <= '01-OCT-2001'
AND B.USER_EXPIRED(+) >= '01-OCT-2001'

 

[CrystalVisualBOracle]

Great! Thanks to you both!!!

CrystalVisualBOracle