pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@echna.ne.jp>
To: pgsql-sql@postgresql.org
Subject: Re: Single VIEW, Everybody JOIN!
Date: Sun, 11 Nov 2001 00:11:53 +0900
Message-ID: <20011110235817.B575.RK73@echna.ne.jp> (raw)
In-Reply-To: <20011107210133.8483.cpmta@c001.snv.cp.net>
References: <20011107210133.8483.cpmta@c001.snv.cp.net>

On 7 Nov 2001 13:01:33 -0800
wyatt wrote:

> 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
> );
> 


It seems that your VIEW shows the combination with LEFT JOINs
like a diagram 1, but a VIEW you want to create including all
of the books in your library, in my considered opinion, needs
to show the combination with FULL OUTER JOIN like a diagram 2.
Because some books in sets and series overlap against different
columns(between name in the set and name in the series), a VIEW
needs not a GROUP BY in order to get rid of the overlaps, but
a FULL OUTER JOIN.  The following query may be just or not be
just as you say, but at least ought to get to the point, I guess.



DROP VIEW booklist;
CREATE VIEW booklist AS 
SELECT b1.ibn, b1.title,
       a1.last, a1.first, a1.middle,
       t3.name, t3.place, t3.set 
   FROM (book AS b1 INNER JOIN (bookauthor AS ba1 INNER JOIN 
                                author AS a1 ON (ba1.ian = a1.ian)
                               ) ON (b1.ibn = ba1.ibn)
        )
        left join
        (SELECT CASE WHEN t1.ibn IS NOT NULL THEN t1.ibn ELSE t2.ibn
                END AS ibn,
                t1.name, t1.place, t2.set
            FROM (SELECT bs1.ibn, s1.name, bs1.place
                     FROM bookseries AS bs1 INNER JOIN
                          series AS s1 ON (bs1.isn = s1.isn)
                 ) AS t1 FULL OUTER JOIN
                 (SELECT bs2.ibn, s2.name AS set
                     FROM bookset AS bs2 INNER JOIN
                          set AS s2 ON (bs2.ign = s2.ign)
                 ) AS t2 ON (t1.ibn = t2.ibn) 
        ) AS t3 ON (b1.ibn = t3.ibn)
;


                   Query diagram 1
-----------------------------------------------------------

book                 bookauthor
[ibn]----+---------->[ibn]            author
[title*] |           [ian]----------->[ian] 
         |                            [last*]
         |                            [first*]
         |                            [middle*]
         |           bookset
         +---------->[ibn]            set
         |           [ign]----------->[ign]
         |                            [name*]
         |
         |           bookseries
         +---------->[ibn]            series
                     [isn]----------->[isn]
                     [place*]         [name*]




                      Query diagram 2
------------------------------------------------------------

book                 bookauthor
[ibn]----+-----------[ibn]            author
[title*] |           [ian]------------[ian] 
         |                            [last*]
         |                            [first*]
         |                            [middle*]
         |
         |                  +----FULL OUTER JOIN------------+ 
         |                  |     bookset                   |
         |                  | +-->[ibn]            set      |
         |                  | |   [ign]------------[ign]    |
         |  alias table     | |                    [name*]  |
         +->[ibn] ..........| |(overlap)                    |
            [set_name*]     | |                             |
            [series_name*]  | |   bookseries                |
            [place*]        | +-->[ibn]            series   |
                            |     [isn]------------[isn]    | 
                            |     [place*]         [name*]  |
                            +-------------------------------+  

                        "---->" means "LEFT OUTER JOIN"
                        "-----" means "INNER JOIN"
                        "<--->" mesns "FULL OUTER JOIN"


Regards,
Masaru Sugawara




view thread (2+ messages)

Message-ID: <20011110235817.B575.RK73@echna.ne.jp>
Permalink:  ../20011110235817.B575.RK73@echna.ne.jp/
Also on:    postgresql.org/message-id/20011110235817.B575.RK73@echna.ne.jp

 · 

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: rk73@echna.ne.jp
  Subject: Re: Single VIEW, Everybody JOIN!
  In-Reply-To: <20011110235817.B575.RK73@echna.ne.jp>

* 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