Re: Need help understanding LEFT JOIN.

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'
> ...
>
> hth
> Stephan
>
> John Peterson schrieb:
>
> > (Oracle 8.1.7 on Win2K)
> >
> > Hello, all!
> >
> > I'm having a little trouble understanding how a LEFT JOIN works.
Consider
> > the following:
> >
> > create table Test(ElementId number not NULL,
> > Ordinal number not NULL,
> > Field varchar2(255),
> > Data varchar2(4000));
> >
> > insert into Test values (1, 0, 'Primary', NULL);
> > insert into Test values (2, 0, 'Primary', NULL);
> > insert into Test values (3, 0, 'Primary', NULL);
> > insert into Test values (4, 0, 'Primary', NULL);
> > insert into Test values (5, 0, 'Primary', NULL);
> >
> > insert into Test values (1, 1, 'ColA', 'ColA Data for 1');
> > insert into Test values (1, 2, 'ColB', 'ColB Data for 1');
> >
> > insert into Test values (2, 1, 'ColC', 'ColC Data for 2');
> >
> > insert into Test values (3, 1, 'ColC', 'ColC Data for 3');
> > insert into Test values (3, 2, 'ColA', 'ColA Data for 3');
> > insert into Test values (3, 3, 'ColB', 'ColB Data for 3');
> >
> > insert into Test values (4, 1, 'ColB', 'ColB Data for 4');
> >
> > 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'
> > and tColB.Field = 'ColB'
> > and tColC.Field = 'ColC'
> >
> > I would expect (or, more precisely, want ;-) the output to be:
> >
> > ElementId DataColA DataColB DataColC
> > --------- --------------- --------------- ---------------
> > 1 ColA Data for 1 ColB Data for 1 (null)
> > 2 (null) (null) ColC Data for 2
> > 3 ColA Data for 3 ColB Data for 3 ColC Data for 3
> > 4 (null) ColB Data for 4 (null)
> > 5 (null) (null) (null)
> >
> > However, much to my surprise, the output is:
> >
> > ElementId DataColA DataColB DataColC
> > --------- --------------- --------------- ---------------
> > 3 ColA Data for 3 ColB Data for 3 ColC Data for 3
> >
> > For some reason, I appear to *only* be getting those rows that represent
the
> > least common denominator (typically non-NULL). Just like a "regular"
JOIN
> > operation
> >
> > Clearly my understanding of how the LEFT JOIN works is a bit "off".
What am
> > I doing wrong?
> >
> > If I were to reconstruct my query to look like:
> >
> > select q01.ElementId,
> > q02.Data as DataColA,
> > q03.Data as DataColB,
> > q04.Data as DataColC
> > from
> > (
> > select ElementId
> > from Test
> > where Field = 'Primary'
> > ) q01,
> > (
> > select ElementId,
> > Data
> > from Test
> > where Field = 'ColA'
> > ) q02,
> > (
> > select ElementId,
> > Data
> > from Test
> > where Field = 'ColB'
> > ) q03,
> > (
> > select ElementId,
> > Data
> > from Test
> > where Field = 'ColC'
> > ) q04
> > Where q01.ElementId = q02.ElementId(+)
> > and q01.ElementId = q03.ElementId(+)
> > and q01.ElementId = q04.ElementId(+)
> >
> > This works as I expected! But, I would have thought the original query
> > would have yielded in the same results.
> >
> > Is there a way to make the first technique work as desired? The second
> > technique is a pretty poor performer when there's a lot of rows in the
Test
> > table. In fact, a VERY poor performer.
> >
> > I would welcome any thoughts/comments/suggestions! Thanks! :-)
> >
> > John Peterson
>
> --
> Mit freundlichen Gr$B|_(Ben
>
> Stephan Langer,
> QITS GmbH
>
>
Received on Mon Oct 29 2001 - 08:13:58 CST