From: | wyatt(at)draggoo(dot)com |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Single VIEW, Everybody JOIN! |
Date: | 2001-11-07 21:01:33 |
Message-ID: | 20011107210133.8483.cpmta@c001.snv.cp.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Fankhauser | 2001-11-07 22:22:13 | Re: RIGHT JOIN is only supported with mergejoinable join conditions |
Previous Message | Claus Heiko Niesen | 2001-11-07 21:00:40 | Can this query be optimized? |