Help With complex SELECT

[SJ]

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.*,Sta tEntry.* from Employees JOIN StatEntry ON
employees.uid == statentry.uid WHERE timestamp in (select
MAX(timestamp) from statentry where uid=employees.u id).

Anyone db guru's out there?
-SJ

[SJ]

Re: Help With complex SELECT

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 .comwrote:

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.*,Sta tEntry.* from Employees JOIN StatEntry ON
employees.uid == statentry.uid WHERE timestamp in (select
MAX(timestamp) from statentry where uid=employees.u id).
>
Anyone db guru's out there?
-SJ

[Roy Harvey]

Re: Help With complex SELECT

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 .comwrote:

>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 .comwrote:

>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.*,Sta tEntry.* from Employees JOIN StatEntry ON
>employees.ui d == statentry.uid WHERE timestamp in (select
>MAX(timestam p) from statentry where uid=employees.u id).
>>
>Anyone db guru's out there?
>-SJ

>

[SJ]

Re: Help With complex SELECT

On Jan 30, 7:49 pm, "SJ" <sjour...@gmail .comwrote:

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 .comwrote:
>
>
>

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.*,Sta tEntry.* from Employees JOIN StatEntry ON
employees.uid == statentry.uid WHERE timestamp in (select
MAX(timestamp) from statentry where uid=employees.u id).

>

Anyone db guru's out there?
-SJ- Hide quoted text -

>
- Show quoted text -

Thank you very very much!

[Roy Harvey]

Re: Help With complex SELECT

On 31 Jan 2007 10:00:39 -0800, "SJ" <sjourdan@gmail .comwrote:

>Thank you so much, This is perfect. However, when I do dbUID =
>result.Fields. Item("uid") always get a null
>If I do for each over the result set, it comes back fine. Any ideas
>on that? should it be someting like dbUID =
>result.Fields. Item("a.uid")?
>
>Thanks again.

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

Roy Harvey
Beacon Falls, CT