From: Raj Mathur <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Date: Fri, 25 Sep 2009 08:36:48 +0530
User-Agent: KMail/1.12.1 (Linux/2.6.30-1-686; KDE/4.3.1; i686; ; )
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
In-Reply-To: <200909241616.36933.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200909250836.48639.raju@linux-delhi.org>

On Thursday 24 Sep 2009, Gary Stainburn wrote:
> Hi folks.
> 
> I have two tables
> 
> create table orders (
> o_id serial primary key
> ...
> );
> 
> create table orders_log (
> ol_id serial primary key,
> o_id int4 not null references orders(o_id),
> ol_timestamp timestamp,
> ol_user,
> );
> 
> How can I select all from orders and the last (latest) entry from the
> orders_log?

Does this do what you want?  You would have to do some tricks if you 
also want orders that don't have any entry in the orders_log table to be 
displayed.

select ol.ol_id,ol.o_id,ol.ol_timestamp
from orders o natural join orders_log ol
where (ol.o_id,ol.ol_timestamp)
  in (select o_id,max(ol_timestamp) from orders_log group by o_id);

Regards,

-- Raju
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves