Re: simple (?) join

From: "Oliveiros C," <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "justin" <justin(at)emproshunts(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>, <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
Subject: Re: simple (?) join
Date: 2009-09-25 11:00:17
Message-ID: 1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Claus Guttesen 2009-09-25 22:36:16 Re: simple (?) join
Previous Message Raj Mathur 2009-09-25 03:06:48 Re: simple (?) join