pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExtraordinary Full Join
4+ messages / 3 participants
[nested] [flat]
* Extraordinary Full Join
@ 2003-04-06 05:50 CN <cnliou9@fastmail.fm>
0 siblings, 1 reply; 4+ messages in thread
From: CN @ 2003-04-06 05:50 UTC (permalink / raw)
To: pgsql-sql
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 | |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 resuts?
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?
TIA
CN
--
http://www.fastmail.fm - Choose from over 50 domains or use your own
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extraordinary Full Join
@ 2003-04-06 17:05 Josh Berkus <josh@agliodbs.com>
parent: CN <cnliou9@fastmail.fm>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2003-04-06 17:05 UTC (permalink / raw)
To: CN <cnliou9@fastmail.fm>; pgsql-sql
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.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.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extraordinary Full Join
@ 2003-04-07 04:00 CN <cnliou9@fastmail.fm>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: CN @ 2003-04-07 04:00 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extraordinary Full Join
@ 2003-04-10 13:21 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 4+ messages in thread
From: Christoph Haller @ 2003-04-10 13:21 UTC (permalink / raw)
To: cnliou9@fastmail.fm; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-10 13:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-06 05:50 Extraordinary Full Join CN <cnliou9@fastmail.fm>
2003-04-06 17:05 ` Josh Berkus <josh@agliodbs.com>
2003-04-07 04:00 ` CN <cnliou9@fastmail.fm>
2003-04-10 13:21 Re: Extraordinary Full Join Christoph Haller <ch@rodos.fzk.de>
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