Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 590E02E1A50 for ; Wed, 14 Nov 2007 07:06:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 98314-10 for ; Wed, 14 Nov 2007 07:06:38 -0400 (AST) Received: from smtp10.kymp.net (smtp10.kymp.net [80.248.96.136]) by postgresql.org (Postfix) with ESMTP id 039432E072B for ; Wed, 14 Nov 2007 07:06:37 -0400 (AST) Received: from kymifedora ([213.130.241.66]) by smtp10.kymp.net (8.13.6/8.13.6) with ESMTP id lAEB6ZBp001441 for ; Wed, 14 Nov 2007 13:06:35 +0200 From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= Reply-To: aarni@kymi.com Organization: KYMI.com To: "pgsql-sql@postgresql.org" Subject: Originally created and last_mod by whom and when ? Date: Wed, 14 Nov 2007 13:06:34 +0200 User-Agent: KMail/1.6.2 MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200711141306.34996.aarni@kymi.com> X-Virus-Scanned: by amavisd-new X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/59 X-Sequence-Number: 29587 Hello, In a web app (Pg 8.2.4 + php) I have product and other tables with fields like product_created timestamp without time zone product_created_user_id integer product_last_mod timestamp without time zone product_last_mod_user_id integer The person who last modified an item can obviously be someone else who originally created it. I can get the names and timestamps with two separate queries but how can I do a single query to get the names of both ? product_id | 1 ... product_created_user_id | 1 product_last_mod_user_id | 2 ID 1, created by X / date and time, last_mod by Y / date and time And a similar query to only one table, users ? user_id integer user_forename text ... user_created timestamp without time zone user_created_user_id integer user_last_mod timestamp without time zone user_last_mod_user_id integer ID 4, name Z, created by X / date and time, last_mod by Y / date and time Join, sub select ? I tried some but only managed to get only one name, errors, nothing at all or two rows with inner join + union ... Lotsa thanks for any help, Aarni --