Postgres Pro
Downloads
Home   >   mailing lists

Thread: Extraordinary Full Join

Extraordinary Full Join

From
"CN"
Date:
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 |        |hobby1m1 | John |
|hobby2

or
id | name |  book  | hobby
----+------+--------+-------m1 | John |        |hobby1m1 | John |        |hobby2m1 | John | book1  |m1 | John | book2
|

or
id | name |  book  | hobby
----+------+--------+-------m1 | John |        |hobby1m1 | John | book1  |m1 | John |        |hobby2m1 | 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  |hobby1m1 | 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

Re: Extraordinary Full Join

From
Josh Berkus
Date:
CN:

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

You'll need to add an "sort_no" column to both tbook and thobby, and ensure
somehow that both tables have the exact same sequence of sort numbers.  Then
you can:

select tmaster.id, tmaster.name, tbook.book, thobby.hobby
from tmaster, tbook, thobbby
where tmaster.id = tbook.id and tmaster.id = thobby.idand tbook.sort_no = thobby.sort_no
order by name, tbook.sort_no

There would also be ways for you to account for having a different number of
books or hobbies, but that would be a much more complex query.


--
-Josh BerkusAglio Database SolutionsSan Francisco

Re: Extraordinary Full Join

From
"CN"
Date:
Thank you! Josh,

> You'll need to add an "sort_no" column to both tbook and thobby, and
> ensure somehow that both tables have the exact same sequence of sort numbers. 
> Then you can:
> 
> select tmaster.id, tmaster.name, tbook.book, thobby.hobby
> from tmaster, tbook, thobbby
> where tmaster.id = tbook.id 
>     and tmaster.id = thobby.id
>     and tbook.sort_no = thobby.sort_no
> order by name, tbook.sort_no
> 
> There would also be ways for you to account for having a different number
> of 
> books or hobbies, but that would be a much more complex query.

Perhaps I did not make my requirement clear. My ultimate goal is to

(1) list not null values first in columns book and hobby
(2) list book and hobby in either descending or ascending order

For example, improving

id | name |  book  | hobby
---+------+--------+-------
m1 | John |        |hobby1
m1 | John | book2  |
m1 | John |        |hobby2
m1 | John | book1  |hobby3

to gain (for descending)

id | name |  book  | hobby
---+------+--------+-------
m1 | John | book2  |hobby3
m1 | John | book1  |hobby2
m1 | John |        |hobby1

or gain (for ascending)

id | name |  book  | hobby
---+------+--------+-------
m1 | John | book1  |hobby1
m1 | John | book2  |hobby2
m1 | John |        |hobby3

I don't want to add "sort_to" column to any table because I have more
tables similar to book and hobby. For example,

CREATE TABLE tfriend(id TEXT, friend TEXT);
CREATE TABLE tfriend(id TEXT, education TEXT);

and hopefully to get

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

Do I have any chance to achieve the ultimate goal?
I need, at least, the correct result for the worst case like:

id | name |  book  | hobby
---+------+--------+-------
m1 | John | book1  |
m1 | John |        | hobby2
m1 | John | book2  | 
m1 | John |        | hobby1

Regards,

CN

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own

Re: Extraordinary Full Join

From
Christoph Haller
Date:
>
> 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