Problems with outer joint

[sashasol]

Hello,

I'm executing the following query in Oracle 9:

select t1.f1, t2.f2, t3.f3
from t1, t2, t3
where t1.f3=t2.f3(+) and t2.f4=t3.f4
and t1.f1 in (date1, date2, date3)

I expect to get all the dates in the result, but I get only those dates that are not null in table t2.(outer joint is ignored) It happens because of the joint t2.f4=t3.f4

I added the following condition:
and (t2.f4=t3.f4 or t2.f4 is null)
In that case I get all the dates, but
those who have values appear twice -
with the values and empty row

How can I get the correct results with all the dates?

Thank you in advance.
Alexandra

[amitpatel66]

I am not able to understand your requirement clearly. In the previous query you said you are getting only NOT NULL values and in the second you said you are getting values twice, once with NOT NULL and one with NULL. Please mention clearly what you are exactly looking at?

[sashasol]

I needed to get the results for all the dates (date1, date2, date3), even if there is no such data in table t2.

I found it may be done if select statement looks like this:

select t1.f1, tt.f2, tt.f5 from t1,
(select t3.f5, t2.f2, t2.f3 from t2, t3 where t3.f4=t2.f4(+)) tt
where t1.f3=tt.f3(+)
and t1.f1 in(date1, date2, date3)

Many thanks for your quick response.

Alexandra

[amitpatel66]

Good work. :). Good to hear your problem is resolved. Do let us know in case of any further issues