Re: Join from de-normalized table

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 Wed Aug 14 2002 - 15:21:39 CEST