Aarni Ruuhimäki wrote:
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 ?
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 = u_cre.id
LEFT JOIN
app_users u_mod ON p.product_last_mod_user_id = u.mod.id
;
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
