Message-ID: <473ADBF7.1060908@archonet.com>
Date: Wed, 14 Nov 2007 11:28:55 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.6 (X11/20070728)
MIME-Version: 1.0
To: aarni@kymi.com
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Originally created and last_mod by whom and when ?
References: <200711141306.34996.aarni@kymi.com>
In-Reply-To: <200711141306.34996.aarni@kymi.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Aarni Ruuhim=E4ki wrote:
> Hello,
>=20
> In a web app (Pg 8.2.4 + php) I have product and other tables with fiel=
ds like
>=20
> product_created timestamp without time zone
> product_created_user_id integer
> product_last_mod timestamp without time zone
> product_last_mod_user_id integer
>=20
> The person who last modified an item can obviously be someone else who=20
> originally created it.
>=20
> I can get the names and timestamps with two separate queries but how ca=
n I do=20
> a single query to get the names of both ?

Alias the tables, so you can join to the user-table twice.

SELECT p.*, u_cre.username as created_by, u_mod.username as modified_by
FROM
   products p
LEFT JOIN
   app_users u_cre ON p.product_created_user_id =3D u_cre.id
LEFT JOIN
   app_users u_mod ON p.product_last_mod_user_id =3D u.mod.id
;


--=20
   Richard Huxton
   Archonet Ltd