From: Josh Berkus <josh@agliodbs.com>
To: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Re: Extraordinary Full Join
Date: Sun, 6 Apr 2003 10:05:58 -0700
Message-ID: <200304061005.58820.josh@agliodbs.com> (raw)
In-Reply-To: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
References: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
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
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: josh@agliodbs.com, cnliou9@fastmail.fm
Subject: Re: Extraordinary Full Join
In-Reply-To: <200304061005.58820.josh@agliodbs.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