Re: [SQL] simple (?) join

[Oliveiros C,]

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@postgresql.org ; gary.stainb...@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

Re: [SQL] simple (?) join

[Claus Guttesen]

> I have two tables
>
> 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?

Maby this will work?

Get the unique o_id from the table orders and do a group by in a
subselect, and then select the other fields from the same table, ie:

select o_od, max(o_field1), max(o_field2) ... from orders where o_id
in (select o_id from orders group by o_id) group by o_id order by
o_id;

This off course assumes that o_id is a serial (and thus that timestamp
will always be higher etc.).

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentler gamester is the soonest winner.

Shakespeare

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql