Re: Need help understanding LEFT JOIN.

From: John Peterson <johnp_at_azstarnet.com>
Date: Mon, 29 Oct 2001 10:52:59 -0700
Message-ID: <ttr5rthdthb860@corp.supernews.com>

Thanks, Stephan!

I guess I had assumed that I could do the equality on the Field value, but that the join (on ElementId) would be the left join aspect. But, I think I'm starting to see what you mean.

In SQL Server, I would have expressed the query thusly:

select t.ElementId,

       tColA.Data as DataColA,
       tColB.Data as DataColB,
       tColC.Data as DataColC
  from      Test as t
  left join Test as tColA on tColA.ElementId = t.ElementId
                         and tColA.Field     = 'ColA'
  left join Test as tColB on tColB.ElementId = t.ElementId
                         and tColB.Field     = 'ColB'
  left join Test as tColC on tColC.ElementId = t.ElementId
                         and tColC.Field     = 'ColC'

 where t.Field = 'Primary'

And maybe that's what made me think I could say "Field = <value>" instead of "Field(+) = <value>".

Thanks again, Stephan! :-)

"Stephan Langer" <slanger_at_dixi-wc.de> wrote in message news:3BDD7016.1A936E5F_at_dixi-wc.de...
> Hallo John,
>
> the left join (+) - operator means this side has to be compared (not
necessarily
> equal) to the other side, if the joined table exists. If the joined table
does
> not exist, this part of the sql will not be evaluated.
>
> Hence, if you outer join (left join) a table (or more than one table) all
> columns of that table(s) have to be used with the (+) - operator. Any
usage
> without (+) will result in an inner join for that column.
>
> Stephan Langer
>
> John Peterson schrieb:
>
> > Hello, Stephan!
> >
> > 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.
> >
> > Thanks again! :-)
> >
> > John Peterson
> >
> > "Stephan Langer" <slanger_at_dixi-wc.de> wrote in message
> > news:3BDD20B7.991A19BF_at_dixi-wc.de...
> > > Hallo,
> > >
> > > you have to "left join" the constants too, like
> > >
> > > ...
> > > where t.Field = 'Primary'
> > > and t.ElementId = tColA.ElementId(+)
> > > and t.ElementId = tColB.ElementId(+)
> > > and t.ElementId = tColC.ElementId(+)
> > > -> and tColA.Field(+) = 'ColA'
> > > -> and tColB.Field(+) = 'ColB'
> > > -> and tColC.Field(+) = 'ColC'
>
Received on Mon Oct 29 2001 - 11:52:59 CST