Re: simple (?) join

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
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves