X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BB2CB4762F6
	for <pgsql-sql@postgresql.org>; Sun,  6 Apr 2003 00:50:22 -0500 (EST)
Received: from smtp.us.messagingengine.com (ny3.fastmail.fm [66.111.4.4])
	by postgresql.org (Postfix) with ESMTP id 40E7B476182
	for <pgsql-sql@postgresql.org>; Sun,  6 Apr 2003 00:50:22 -0500 (EST)
Received: from smtp.us2.messagingengine.com (server2.internal [10.202.2.133])
	by server3.messagingengine.com (Postfix) with ESMTP id 7EE7353B10
	for <pgsql-sql@postgresql.org>; Sun,  6 Apr 2003 00:50:17 -0500 (EST)
Received: from 127.0.0.1 ([127.0.0.1] helo=smtp.us2.messagingengine.com) by
	messagingengine.com with SMTP; Sun, 06 Apr 2003 00:50:17 -0500
Received: by smtp.us2.messagingengine.com (Postfix, from userid 99)
	id 6E7054FDFB; Sun,  6 Apr 2003 00:50:17 -0500 (EST)
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="Big5"
MIME-Version: 1.0
X-Mailer: MIME::Lite 1.2  (F2.71; T1.001; A1.51; B2.12; Q2.03)
From: "CN" <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Date: Sat, 05 Apr 2003 21:50:17 -0800
X-Epoch: 1049608217
X-Sasl-enc: 6U+BhLQViTKIm3myYlTsnA
Subject: Extraordinary Full Join
Message-Id: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
X-Spam-Status: No, hits=0.0 required=5.0
	tests=none
	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/81
X-Sequence-Number: 12782

Hello! postgreSQL lovers,

Master table tmaster has 2 childern tables tbook and thobby.

CREATE TABLE tmaster (id TEXT,name TEXT);
CREATE TABLE tbook (id TEXT, book TEXT);
CREATE TABLE thobby (id TEXT, hobby TEXT);

INSERT INTO tmaster VALUES ('m1','John');
INSERT INTO tmaster VALUES ('m2','Mary');

INSERT INTO tbook VALUES ('m1','book1');
INSERT INTO tbook VALUES ('m1','book2');

INSERT INTO thobby VALUES ('m1','hobby1');
INSERT INTO thobby VALUES ('m1','hobby2');

I want to list John's books and hobbies in one table:

 id | name |  book  | hobby
----+------+--------+-------
 m1 | John | book1  |
 m1 | John | book2  |
 m1 | John |        |hobby1
 m1 | John |        |hobby2

or

 id | name |  book  | hobby
----+------+--------+-------
 m1 | John |        |hobby1
 m1 | John |        |hobby2
 m1 | John | book1  |
 m1 | John | book2  |

or

 id | name |  book  | hobby
----+------+--------+-------
 m1 | John |        |hobby1
 m1 | John | book1  |
 m1 | John |        |hobby2
 m1 | John | book2  |

etc.

What is the SQL to make any one of above resuts?

Even better, if possible, "sort" book and hobby column descendently to
fill up null columns:

 id | name |  book  | hobby
----+------+--------+-------
 m1 | John | book1  |hobby1
 m1 | John | book2  |hobby2

What is the better-have SQL to produce the last furnished list?

TIA

CN

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own