>
> 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 tbookunion
select id,'hobby',hobby from thobby;
select * from vattribute order by 1,2,3;id | attnam | attval
----+--------+--------m1 | book | book1m1 | book | book2m1 | hobby | hobby1m1 | hobby | hobby2m1 | 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 | | hobby1m1 | | hobby2m1 | | hobby3
(5 rows)
select * from vattdetail order by 1,2 desc,3 desc;id | books | hobbies
----+-------+---------m1 | | hobby3m1 | | hobby2m1 | | hobby1m1 | 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
