Re: Need help understanding LEFT JOIN.

Hello, Nicholas!

Ah...that makes sense! Thank you for the clarification! I think I'm starting to understand it better, now.

Thanks again! :-)

John Peterson

"Nicholas Carey" <ncarey_at_speakeasy.org> wrote in message news:Xns9149B246087FFncareyspeakeasyorg_at_207.126.101.92...
> On 29 Oct 2001, "John Peterson" <johnp_at_azstarnet.com> spake
> and said:
>
> > Thank you so much for the info! I gave it a try, and it
> > surely appears to work! I don't quite understand it, but
> > I'll surely give this a try.
>
> In your example, since the tests against constants in the WHERE
> clause do not participate in the join itself -- no left join
> '(+)' -- the tests are effectively applied post-join.
>
> You could also, in addition to including the constant tests in
> the join criteria, correct your query by changing the tests.
> You need to test for nullity as well as testing for the constant
> value:
>
> select t.ElementId ,
> tColA.Data as DataColA ,
> tColB.Data as DataColB ,
> tColC.Data as DataColC
> from Test t ,
> Test tColA ,
> Test tColB ,
> Test tColC
> where t.Field = 'Primary'
> and t.ElementId = tColA.ElementId(+)
> and t.ElementId = tColB.ElementId(+)
> and t.ElementId = tColC.ElementId(+)
> and ( tColA.Field = 'ColA' -- filter matching rows
> OR tColA.ElementID is NULL -- but accept unmatched.
> )
> and ( tColB.Field = 'ColB' -- filter matching rows
> OR tColB.ElementIDis NULL -- but accept unmatched.
> )
> and ( tColC.Field = 'ColC' -- filter matching rows
> OR tColC.ElementID is NULL -- but accept unmatched.
> )
>
> --
Received on Tue Oct 30 2001 - 09:16:20 CST