Re: Single VIEW, Everybody JOIN! - Mailing list pgsql-sql

On 7 Nov 2001 13:01:33 -0800
wyatt wrote:

> After looking at some other scary nested LEFT JOINs from the list
> (thanks Josh), I have determined that I still don't know the difference
> between a normal JOIN, a LEFT JOIN and a RIGHT JOIN, but I think I got
> the whole library thing to work.
> 
> Now, can someone tell me if this is the way it is supposed to look, of if
> it's just nuts?
> 
> And finally, what should I do about series with different authors for each
> book --- how do I get them together in the ORDER BY without taking the rest
> of the series away from the rest of the books by their authors?
> 
> DROP VIEW booklist;
> CREATE VIEW booklist AS(
>     SELECT
>         book.title AS title,
>         author.last AS last,
>         author.first AS first,
>         author.middle AS middle,
>         series.name AS series,
>         bookseries.place AS place,
>         set.name AS set
>     FROM
>         (
>             (
>                 book LEFT JOIN
>                 (
>                     bookauthor LEFT JOIN author
>                     ON bookauthor.ian = author.ian
>                 )
>                 ON book.ibn = bookauthor.ibn
>             ) LEFT JOIN (
>                 (
>                     bookseries LEFT JOIN series
>                     ON bookseries.isn = series.isn
>                 )
>             )
>             ON book.ibn = bookseries.ibn
>         ) LEFT JOIN (
>             bookset LEFT JOIN set
>             ON bookset.ign = set.ign
>         )
>         ON book.ibn = bookset.ibn
>     ORDER BY
>         set, last, first, middle, series, place, title
> );
> 


It seems that your VIEW shows the combination with LEFT JOINs
like a diagram 1, but a VIEW you want to create including all
of the books in your library, in my considered opinion, needs
to show the combination with FULL OUTER JOIN like a diagram 2.
Because some books in sets and series overlap against different
columns(between name in the set and name in the series), a VIEW
needs not a GROUP BY in order to get rid of the overlaps, but
a FULL OUTER JOIN.  The following query may be just or not be
just as you say, but at least ought to get to the point, I guess.



DROP VIEW booklist;
CREATE VIEW booklist AS 
SELECT b1.ibn, b1.title,      a1.last, a1.first, a1.middle,      t3.name, t3.place, t3.set   FROM (book AS b1 INNER
JOIN(bookauthor AS ba1 INNER JOIN                                author AS a1 ON (ba1.ian = a1.ian)
        ) ON (b1.ibn = ba1.ibn)       )       left join       (SELECT CASE WHEN t1.ibn IS NOT NULL THEN t1.ibn ELSE
t2.ibn              END AS ibn,               t1.name, t1.place, t2.set           FROM (SELECT bs1.ibn, s1.name,
bs1.place                   FROM bookseries AS bs1 INNER JOIN                         series AS s1 ON (bs1.isn =
s1.isn)               ) AS t1 FULL OUTER JOIN                (SELECT bs2.ibn, s2.name AS set                    FROM
booksetAS bs2 INNER JOIN                         set AS s2 ON (bs2.ign = s2.ign)                ) AS t2 ON (t1.ibn =
t2.ibn)       ) AS t3 ON (b1.ibn = t3.ibn)
 
;

                  Query diagram 1
-----------------------------------------------------------

book                 bookauthor
[ibn]----+---------->[ibn]            author
[title*] |           [ian]----------->[ian]         |                            [last*]        |
    [first*]        |                            [middle*]        |           bookset        +---------->[ibn]
 set        |           [ign]----------->[ign]        |                            [name*]        |        |
bookseries       +---------->[ibn]            series                    [isn]----------->[isn]
[place*]        [name*]
 



                     Query diagram 2
------------------------------------------------------------

book                 bookauthor
[ibn]----+-----------[ibn]            author
[title*] |           [ian]------------[ian]         |                            [last*]        |
    [first*]        |                            [middle*]        |        |                  +----FULL OUTER
JOIN------------+        |                  |     bookset                   |        |                  | +-->[ibn]
      set      |        |                  | |   [ign]------------[ign]    |        |  alias table     | |
     [name*]  |        +->[ibn] ..........| |(overlap)                    |           [set_name*]     | |
             |           [series_name*]  | |   bookseries                |           [place*]        | +-->[ibn]
   series   |                           |     [isn]------------[isn]    |                            |     [place*]
   [name*]  |                           +-------------------------------+  
 
                       "---->" means "LEFT OUTER JOIN"                       "-----" means "INNER JOIN"
     "<--->" mesns "FULL OUTER JOIN"
 


Regards,
Masaru Sugawara