Re: simple (?) join

----- Original Message -----

From: justin

To: David W Noon

Cc: pgsql-sql@postgresql.org ; gary.stainburn@ringways.co.uk

Sent: Thursday, September 24, 2009 9:15 PM

Subject: Re: [SQL] simple (?) join

David W Noon wrote:

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.  

I don't think that is what he is requesting.  I read it he also wants the timestamp included in the result set

A nested query

Select
    orders.*,
    (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid)
From orders

Still another option is using a join

Select
    orders.*, ol_timestamp
    From orders
    left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by o_id) as JoinQuery on JoinQuery.o_id = orders.o_id

The second one should be faster

Hello, Justin, Gary.

 

Justin, your (the second one) query is not much different from mine.

 You previewed  the possibility of having orders without any matching entry on orders_log with your left join, something that I haven't. Gary, will you have

records on your orders table that don't reference any record on your orders_log table? If so, Justin's query is the right one you should use.

 

You return the full record from orders and an additional column from orders_log, the ol_timestamp column. I understood that Gary wanted the full record from orders_log, not

just the timestamp column. That part is done by my subquery .

 

 I think Gary could clarify what he wants exactly. Gary? :)

 

Also, Justin, your query design seems right to me, but maybe you should add this (the part in comment) to your subquery

SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log group by o_id

because the MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably

the parser will complain that column ol_timestamp  does not exist.

Ain't I right?