Re: simple (?) join

<br /><br /> David W Noon wrote: <blockquote cite="mid:20090924194629.44bc236f@dwnoon.ntlworld.com" type="cite"><pre
wrap="">OnThu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL]
 
simple (?) join:
 </pre><blockquote type="cite"><pre wrap="">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?   </pre></blockquote><pre wrap="">
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. </pre></blockquote><br /> I don't think that is what he is requesting.  I read it he also wants the
timestampincluded in the result set<br /><br /> A nested query <br /><br /> Select <br />     orders.*, <br />    
(SELECTMAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid) <br /> From orders<br /><br /> Still
anotheroption is using a join <br /><br /> Select <br />     orders.*, ol_timestamp<br />     From orders <br />    
leftjoin (SELECT MAX(ol_timestamp), o_id FROM orders_log group by o_id) as JoinQuery on JoinQuery.o_id = orders.o_id
<br/><br /> The second one should be faster<br /><br /><br /><br />