pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSingle VIEW, Everybody JOIN!
2+ messages / 2 participants
[nested] [flat]
* Single VIEW, Everybody JOIN!
@ 2001-11-07 21:01 wyatt@draggoo.com
0 siblings, 1 reply; 2+ messages in thread
From: wyatt@draggoo.com @ 2001-11-07 21:01 UTC (permalink / raw)
To: pgsql-sql
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
);
Thanks,
Wyatt
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Single VIEW, Everybody JOIN!
@ 2001-11-10 15:11 Masaru Sugawara <rk73@echna.ne.jp>
parent: wyatt@draggoo.com
0 siblings, 0 replies; 2+ messages in thread
From: Masaru Sugawara @ 2001-11-10 15:11 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-11-10 15:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-07 21:01 Single VIEW, Everybody JOIN! wyatt@draggoo.com
2001-11-10 15:11 ` Masaru Sugawara <rk73@echna.ne.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox