From: Christoph Haller <ch@rodos.fzk.de>
To: cnliou9@fastmail.fm
Cc: pgsql-sql@postgresql.org
Subject: Re: Extraordinary Full Join
Date: Thu, 10 Apr 2003 15:21:06 +0200
Message-ID: <3E956FC1.4D416D08@rodos.fzk.de> (raw)
>
> 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
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: ch@rodos.fzk.de, cnliou9@fastmail.fm
Subject: Re: Extraordinary Full Join
In-Reply-To: <3E956FC1.4D416D08@rodos.fzk.de>
* 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