From: CN <cnliou9@fastmail.fm>
To: josh@agliodbs.com
To: pgsql-sql@postgresql.org
Subject: Re: Extraordinary Full Join
Date: Sun, 06 Apr 2003 20:00:41 -0800
Message-ID: <20030407040041.8DEA64F81@smtp.us2.messagingengine.com> (raw)
In-Reply-To: <200304061005.58820.josh@agliodbs.com>
References: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
<200304061005.58820.josh@agliodbs.com>
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
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: cnliou9@fastmail.fm, josh@agliodbs.com
Subject: Re: Extraordinary Full Join
In-Reply-To: <20030407040041.8DEA64F81@smtp.us2.messagingengine.com>
* 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