From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Extraordinary Full Join
Date: Sat, 05 Apr 2003 21:50:17 -0800
Message-ID: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com> (raw)
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
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: cnliou9@fastmail.fm
Subject: Re: Extraordinary Full Join
In-Reply-To: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
* 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