pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (2+ messages)  latest in thread

Message-ID: <20011107210133.8483.cpmta@c001.snv.cp.net>
Permalink:  ../20011107210133.8483.cpmta@c001.snv.cp.net/
Also on:    postgresql.org/message-id/20011107210133.8483.cpmta@c001.snv.cp.net

 · 

reply

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