Content-Type: text/plain;
  charset="big5"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "CN" <cnliou9@fastmail.fm>, pgsql-sql@postgresql.org
Subject: Re: Extraordinary Full Join
Date: Sun, 6 Apr 2003 10:05:58 -0700
User-Agent: KMail/1.4.3
References: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
In-Reply-To: <20030406055017.6E7054FDFB@smtp.us2.messagingengine.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304061005.58820.josh@agliodbs.com>

CN:

> Even better, if possible, "sort" book and hobby column descendently to
> fill up null columns:
>=20
>  id | name |  book  | hobby
> ----+------+--------+-------
>  m1 | John | book1  |hobby1
>  m1 | John | book2  |hobby2
>=20
> 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=
=20
somehow that both tables have the exact same sequence of sort numbers.  The=
n=20
you can:

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

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


--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco