Re: Join from de-normalized table

Heinz

thanks heaps - this did the trick. The LEFT OUTER JOIN syntax is supported in Oracle 9i, i'm using Oracle-8 so i used the second version.

Appreciate all your help

regards
grant

hhuber_at_racon-linz.at (Heinz Huber) wrote in message news:<3d5a580e.133126615_at_news.aon.at>...
> On 14 Aug 2002 04:22:48 -0700, gsayer1_at_bigpond.net.au (Grant Sayer)
> wrote:
>
> >A question on a SQL query problem. I've inherited a DB (don't you
> >just hate that :-)) which has (as part of this problem) 3 tables;
> >called Articles, Images and SpecialImages. Now the relationship in
> >place is Articles --1-to-many --> SpecialImages and
> >Articles--1-to-1-->Images. So some of the fields are like
> >
> >Article(id, image_id, sp_id1, sp_id2, sp_id3) where image_id is the
> >FK to the Image table, and sp_idX is a foreign key into the
> >SpecialImage table.
> >
> >I want to get a row which shows the article and all the associated
> >images from both other tables. An outer join seems restricted to only
> >two tables.
> >
> >I thought that maybe a UNION might help so that i could do the query
> >for each part and then UNION to give the final result set
>
> I don't know why an outer join should only work for two tables.
>
> If you're using a database that supports the new OUTER JOIN syntax,
> this should work:
> SELECT a.id, i.image, s1.image, s2.image, s3.image
> FROM Article a LEFT OUTER JOIN Images i ON a.image_id = i.image_id
> LEFT OUTER JOIN SpecialImages s1 ON a.sp_id1 = i.sp_id
> LEFT OUTER JOIN SpecialImages s2 ON a.sp_id2 = i.sp_id
> LEFT OUTER JOIN SpecialImages s3 ON a.sp_id3 = i.sp_id;
>
> Otherwise, try:
> SELECT a.id, i.image, s1.image, s2.image, s3.image
> FROM Article a, Images i,
> SpecialImages s1, SpecialImages s2, SpecialImages s3
> WHERE a.image_id = i.image_id (+) AND a.sp_id1 = i.sp_id (+) AND
> a.sp_id2 = i.sp_id (+) AND a.sp_id3 = i.sp_id (+);
>
> This syntax has a problem, when you don't have a scenario like this
> (one master and many details). For example a two level outer join (a
> OUTER JOIN b ON a.id = b.id OUTER JOIN c ON b.xy = c.xy) won't work
> with the (+) syntax, IIRC.
>
> hth,
> Heinz
Received on Thu Aug 15 2002 - 02:23:12 CEST