Re: simple (?) join

From: Raj Mathur <raju(at)linux-delhi(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: simple (?) join
Date: 2009-09-25 03:06:48
Message-ID: 200909250836.48639.raju@linux-delhi.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)kandalaya(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Oliveiros C, 2009-09-25 11:00:17 Re: simple (?) join
Previous Message David W Noon 2009-09-24 21:21:06 Re: simple (?) join