Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 20C0B634C73
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 24 Sep 2009 15:46:41 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 34375-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 24 Sep 2009 18:46:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from karnak.local (cpc3-lutn10-2-0-cust261.9-3.cable.virginmedia.com
	[81.97.85.6])
	by mail.postgresql.org (Postfix) with ESMTP id 4B8DE634BD0
	for <pgsql-sql@postgresql.org>; Thu, 24 Sep 2009 15:46:33 -0300 (ADT)
Received: from dwnoon.ntlworld.com (localhost [127.0.0.1])
	by karnak.local (Postfix) with ESMTP id AC3AC1F003
	for <pgsql-sql@postgresql.org>; Thu, 24 Sep 2009 19:46:30 +0100 (BST)
Date: Thu, 24 Sep 2009 19:46:29 +0100
From: David W Noon <dwnoon@ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Message-ID: <20090924194629.44bc236f@dwnoon.ntlworld.com>
In-Reply-To: <200909241616.36933.gary.stainburn@ringways.co.uk>
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
X-Mailer: Claws Mail 3.7.2 (GTK+ 2.16.6; i686-pc-linux-gnu)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.772 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, RCVD_IN_PBL=0.905, RCVD_IN_SORBS_DUL=0.877,
	RDNS_DYNAMIC=0.1
X-Spam-Level: 
X-Archive-Number: 200909/119
X-Sequence-Number: 33184

On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL]
simple (?) join:

>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?

SELECT * FROM orders
WHERE o_id IN (SELECT o_id FROM orders_log
   WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log));

No joins required.
-- 
Regards,

Dave  [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================