Problem in using left outer join...

[job1]

Hi
I have pasted my query below and my query follows it

Select
a.*
b.*
from
table a LEFT OUTER JOIN table2 b ON
a.EMPLID = b.EMPLID
AND YEAR(a.EFFDT) = YEAR(b.P1_ACCR_EFFDT)
Where
( b.EFFDT IN
(SELECT (c.EFFDT)
FROM
table2 c
WHERE
c.EMPLID = b.EMPLID
AND c.EMPL_RCD# = b.EMPL_RCD#
AND c.EFFDT <= ‘patameter date’
OR b.EFFDT IS NULL)

1. If there are records for a particular employee in table1 a and also in table 2 b and the effective date is <= the given(parameter) date then the appropreate value gets displayed.
2. If there is record for a employee in table1 a and no record in table2 b even then the employee gets displayed in the report with all the table2 b values as null.

Expected logic
3. Now if there is records for the employee in table1 a and also in table2 b but the record contain effective date > the given(parameter) date so inspite of the outer join the entire record is not getting displayed. Here I wanted to display this record with the table2 b values as null.

Its urgent can anyone help me in this.

 

[r937]

change the first word WHERE to AND

rudy

http://r937.com/

SQL Consulting

 

[job1]

Thx for your reply... I am using a tool where I cannot change the from clause(i.e. I cannot append conditions to the outer join). Is there any other way to sort my problem using where clause itself.

I have a alternative using Union which I dont want to use it as it affects the performance...

Please revert...