X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 92739476390
	for <pgsql-sql@postgresql.org>; Thu, 10 Apr 2003 09:22:35 -0400 (EDT)
Received: from fzkmail2.fzk.de (unknown [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id 83C2B4763A6
	for <pgsql-sql@postgresql.org>; Thu, 10 Apr 2003 09:22:27 -0400 (EDT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Thu Apr 10 15:21:08 2003 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id PAA09487; Thu, 10 Apr 2003 15:21:06 +0200 (METDST)
Message-ID: <3E956FC1.4D416D08@rodos.fzk.de>
Date: Thu, 10 Apr 2003 15:21:06 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: cnliou9@fastmail.fm
Cc: pgsql-sql@postgresql.org
Subject: Re: Extraordinary Full Join
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Spam-Status: No, hits=-9.6 required=5.0
	tests=BAYES_10,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/157
X-Sequence-Number: 12858

>
> 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 results?
>
My approach would be

INSERT INTO thobby VALUES ('m1','hobby3');

create view vattribute(id,attnam,attval) as
select id,'book',book from tbook
 union
select id,'hobby',hobby from thobby;

select * from vattribute order by 1,2,3;
 id | attnam | attval
----+--------+--------
 m1 | book   | book1
 m1 | book   | book2
 m1 | hobby  | hobby1
 m1 | hobby  | hobby2
 m1 | hobby  | hobby3
(5 rows)

create view vattdetail(id,books,hobbies) as
select id,
case attnam when 'book' then attval else null end,
case attnam when 'hobby' then attval else null end
from vattribute;

select * from vattdetail order by 1,2,3;
 id | books | hobbies
----+-------+---------
 m1 | book1 |
 m1 | book2 |
 m1 |       | hobby1
 m1 |       | hobby2
 m1 |       | hobby3
(5 rows)

select * from vattdetail order by 1,2 desc,3 desc;
 id | books | hobbies
----+-------+---------
 m1 |       | hobby3
 m1 |       | hobby2
 m1 |       | hobby1
 m1 | book2 |
 m1 | book1 |
(5 rows)

Later you mentioned you are going to have additional stuff like
CREATE TABLE tfriend(id TEXT, friend TEXT);
CREATE TABLE teducat(id TEXT, education TEXT);

id | name |  book  | hobby | friend  | education
---+------+--------+-------+---------+-----------
m1 | John | book2  |hobby3 | FriendA | Edu C
m1 | John | book1  |hobby2 |         | Edu B
m1 | John |        |hobby1 |         | Edu A

It is possible to generate the view above dynamically via plpgsql
to ease up adding more attributes.

>
> 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
>
No idea how to achieve that. Maybe a perl script could do that.

Regards, Christoph