From: wyatt@draggoo.com
To: pgsql-sql@postgresql.org
Subject: Single VIEW, Everybody JOIN!
Date: 7 Nov 2001 13:01:33 -0800
Message-ID: <20011107210133.8483.cpmta@c001.snv.cp.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wyatt@draggoo.com
Subject: Re: Single VIEW, Everybody JOIN!
In-Reply-To: <20011107210133.8483.cpmta@c001.snv.cp.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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