Re: Single VIEW, Everybody JOIN!

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 bookset AS 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