Help With complex SELECT

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SJ - 31 Jan 2007 00:00 GMT

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

Reply to this Message

SJ - 31 Jan 2007 01:49 GMT

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

> Can someone help me with an advanced query?
>
[quoted text clipped - 18 lines]
> Anyone db guru's out there?
> -SJ

Reply to this Message

Roy Harvey - 31 Jan 2007 04:41 GMT

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

>OK, I am close.
>
[quoted text clipped - 34 lines]
>> Anyone db guru's out there?
>> -SJ

Reply to this Message

SJ - 31 Jan 2007 18:00 GMT

> OK, I am close.
>
[quoted text clipped - 36 lines]
>
> - Show quoted text -

Thank you very very much!

Reply to this Message

Roy Harvey - 31 Jan 2007 18:12 GMT

>Thank you so much, This is perfect. However, when I do dbUID =
>result.Fields.Item("uid") always get a null
[quoted text clipped - 3 lines]
>
>Thanks again.

Try removing the quotes. Use result.Fields.Item(uid) rather than
result.Fields.Item("uid"), result.Fields.Item(a.uid) rather than
result.Fields.Item("a.uid").

Roy Harvey
Beacon Falls, CT

Reply to this Message