Posted by Roy Harvey on 01/31/07 04:41

You could try something like:

SELECT a.*, b.*
FROM employees as a
LEFT OUTER
JOIN (SELECT *
FROM statentry as X
WHERE X.timestamp =
(select max(timestamp)
from statentry as Y
where X.uid = Y.uid)) as b
ON a.uid = b.uid

Roy Harvey
Beacon Falls, CT

On 30 Jan 2007 17:49:11 -0800, "SJ" <sjourdan@gmail.com> wrote:

>OK, I am close.
>
>Here is what I have
>
>SELECT a.*,b.* from employees as a LEFT JOIN statentry as b ON
>a.uid=b.uid
>WHERE b.timestamp IN (select MAX(timestamp) from statentry where
>uid=b.uid)
>OR b.timestamp IS NULL
>
>
>This returns me all the values, but for some rease UID in the result
>set is allways null. ANy idea why?
>
>THanks,
>-SJ
>
>On Jan 30, 6:00 pm, "SJ" <sjour...@gmail.com> wrote:
>> Can someone help me with an advanced query?
>>
>> I have two tables
>>
>> Table 1: Employees
>> Cols: UID, lname, fname
>>
>> Table 2: StatEntry
>> Cols: UID, Timestamp, description
>>
>> The queary should reaturn all the information in both tables. If more
>> than one entry exists in the second table, it should return the one
>> with the greatest timestamp. If not entries exist I would like the
>> second table columns set to "no value"
>>
>> Something link:
>> select Employees.*,StatEntry.* from Employees JOIN StatEntry ON
>> employees.uid == statentry.uid WHERE timestamp in (select
>> MAX(timestamp) from statentry where uid=employees.uid).
>>
>> Anyone db guru's out there?
>> -SJ
>

[Back to original message]