Received: from c001.snv.cp.net (c001-h006.c001.snv.cp.net [209.228.32.120]) by postgresql.org (8.11.3/8.11.4) with SMTP id fA7L1Xm25076 for ; Wed, 7 Nov 2001 16:01:33 -0500 (EST) (envelope-from wyatt@draggoo.com) Received: (cpmta 8484 invoked from network); 7 Nov 2001 13:01:33 -0800 Date: 7 Nov 2001 13:01:33 -0800 Message-ID: <20011107210133.8483.cpmta@c001.snv.cp.net> X-Sent: 7 Nov 2001 21:01:33 GMT Received: from [66.69.65.213] by mail.draggoo.com with HTTP; 07 Nov 2001 13:01:33 PST Content-Type: text/plain Content-Disposition: inline Mime-Version: 1.0 To: pgsql-sql@postgresql.org From: wyatt@draggoo.com X-Mailer: Web Mail 3.9.3.5 X-Sent-From: wyatt@draggoo.com Subject: Single VIEW, Everybody JOIN! X-Archive-Number: 200111/114 X-Sequence-Number: 5498 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