Received: from c001.snv.cp.net (c001-h014.c001.snv.cp.net [209.228.32.128]) by postgresql.org (8.11.3/8.11.4) with SMTP id fA72C4m48556 for ; Tue, 6 Nov 2001 21:12:08 -0500 (EST) (envelope-from wyatt@draggoo.com) Received: (cpmta 9607 invoked from network); 6 Nov 2001 18:12:03 -0800 Date: 6 Nov 2001 18:12:03 -0800 Message-ID: <20011107021203.9606.cpmta@c001.snv.cp.net> X-Sent: 7 Nov 2001 02:12:03 GMT Received: from [66.69.65.213] by mail.draggoo.com with HTTP; 06 Nov 2001 18:12:02 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, and FUNCTION questions... X-Archive-Number: 200111/89 X-Sequence-Number: 5473 Hey all, I've got a book database here, and I'm trying to create a single VIEW that lists *all* of the books. Here are the relavent parts: Data tables: book, author, publisher, category, series, set These tables store the appropriate data for each table; for instance, the book table stores the ISBN (if any), title, publisher (index to publisher table), copyright year, and a primary key series. Joining tables: bookauthor, bookcategory, bookseries, bookset These tables are generally columns containing integers for each of the keys to join them; the bookauthor has a book key and an author key, the bookseries has a book key, series key, and the place in the series (book 1, 2, etc). What I am trying to do is get a single VIEW to include *all* of the books, as well as the series, place in the series, and set *if* they have them. But, since not all books belong to a series or set, all I have been able to manage is to get several VIEWs: one with all of the books in a series, one with books in a set, one with books in both series *and* sets, and one with the others --- with some overlap (books in sets *and* series, for instance, end up in all three of the VIEWs). Is there a way to get all of these into a single VIEW with a single query? Or am I going to have to do several queries from Java or Perl and put them together (final project is in Java)? Also, I'm trying to figure out how much should be implemented as FUNCTIONS in the database, and how much should be in the Java/Web portion. For instance, when a book is deleted out of the book table, should a I delete all of the references to that book in the database with a trigger, or do it with the Java portion? Thanks, Wyatt