given the following two tables, i am looking for help writing a query
that returns all rows in table the first table along with the data
from the second time with the latest (newest) timestamp. thanks!

id name
1 A
2 B
3 C
4 D

id time text
1 2010-06-23 20:40:00 first
1 2010-06-23 20:41:00 second
1 2010-06-23 20:42:00 third
2 2010-06-23 20:40:00 first
2 2010-06-23 20:41:00 second
2 2010-06-23 20:42:00 third
3 2010-06-23 20:40:00 first
3 2010-06-23 20:41:00 second
3 2010-06-23 20:42:00 third

the result i would like from the query is:
A third
B third
C third
d null

In article <b70acc0e-9ee6-445f-a13e-fa2ca0fb1e0f (AT) 5g2000yqz (DOT) googlegroups.com>, elubin <elubin1234 (AT) gmail (DOT) com> wrote:
Smells like homework. What have you tried so far, and what happened when you
tried it?

On 24 June, 02:36, elubin <elubin1... (AT) gmail (DOT) com> wrote:
Search this group for the strawberry query

not a homework assignment... rather an app i'm trying to develop...
just don't have strong SQL skills.

strawberry query works great for the first part of my question
(getting the max values from the second table), but still can't figure
out how to successfully LEFT OUTER JOIN so i get all rows from the
first table.

select t1.wait_time,t1.id,t1.insert_time from times t1
where t1.insert_time= (select max(t2.insert_time) from times t2
where t1.id=t2.id)
order by id

Sorry, meant to post the INNER JOIN strawberry type, not the sub-
select...

SELECT t1.text,t1.id,t1.insert_time
FROM times t1
LEFT JOIN times t2 ON t1.id = t2.id AND t1.insert_time <
t2.insert_time
WHERE t2.id IS NULL;

OK, finally got it with a proper second join....

SELECT r.name,t1.wait_time,t1.id,t1.insert_time
FROM tableA r
left outer join tableB t1 on t1.id=r.id
LEFT JOIN tableB t2 ON t1.id = t2.id AND t1.insert_time <
t2.insert_time
WHERE t2.id IS NULL;

On Jun 24, 6:18*pm, elubin <elubin1... (AT) gmail (DOT) com> wrote:
Glad it helped