Re: simple (?) join

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?

Best,
Oliveiros

----- Original Message -----
From: justin
To: David W Noon
Cc: pgsql-sql(at)postgresql(dot)org ; gary(dot)stainburn(at)ringways(dot)co(dot)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