Re: Extraordinary Full Join

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