Help With complex SELECT

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?
-SJOK, 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:

Quote:

Originally Posted by

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

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

Quote:

Originally Posted by

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

Quote:

Originally Posted by

>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

>

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

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Can someone help me with an advanced query?

>

Quote:

Originally Posted by

I have two tables

>

Quote:

Originally Posted by

Table 1: Employees
Cols: UID, lname, fname

>

Quote:

Originally Posted by

Table 2: StatEntry
Cols: UID, Timestamp, description

>

Quote:

Originally Posted by

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"

>

Quote:

Originally Posted by

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).

>

Quote:

Originally Posted by

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

>
- Show quoted text -

Thank you very very much!|||On 31 Jan 2007 10:00:39 -0800, "SJ" <sjourdan@.gmail.comwrote:

Quote:

Originally Posted by

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

Help with Column Widths

How can I find out the sizes of the columns that are returned from a query?
I need to know so that I can pad each one to it's full size with spaces so
that everything will line up using a mono spaced font in a textbox .NET
control. If I fire off a query and get back a DataSet of results, then
when I loop through them and populate a large multiline textbox, each
column's contents are only as long as the data contained in it, not the
full width that the column is designed as in the schema. I'm using c# in
.NET. Is there a way to find out what the full width should be for each
column returned from a query?
-- Rob
Pull ThePlug to reply by email...this sounds like UI formatting again ...use the
Sring.PadRight(totalLength, char)
method to format it to the length yout want...
Message posted via http://www.webservertalk.com|||or if you really want the length of the text
select datalength(description),description from [table]
for each of the columns that you would like to "padright"
Message posted via http://www.webservertalk.com|||In a tiered architecture, the formatting in done in the front end and
not in the database. Doesn't C# have such functions?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1112972652.223954.175560@.g14g2000cwa.googlegroups.com:

> In a tiered architecture, the formatting in done in the front end and
> not in the database. Doesn't C# have such functions?
>
Yes, I want to do it in the front-end. I am going to use the pad()
function, but I don't know the column widths to pass to the pad() function.
This app is very similar to Query Analyzer in that it will allow you to
type in your query and then it will execute it and return the results. I'm
giving the users the option of viewing the results in a DataGrid, or in a
textbox so they can easily "copy and paste" from the text to some other
application. Query Analyzer does this too, and their "text" output mode
has all of the columns presented in a "padded" format, all neat and
aligned. If I fill a textbox control with the returned results from a
query, each column is trimmed of any extra spaces before I get it. I'm
trying to figure out how to put those spaces back before displaying the
results.
Thanks for your reply...
-- Rob
Pull ThePlug to reply by email...|||"baie dronk via webservertalk.com" <forum@.webservertalk.com> wrote in
news:892513b471ec4827844239655f3aabf0@.SQ
webservertalk.com:

> this sounds like UI formatting again ...use the
> Sring.PadRight(totalLength, char)
> method to format it to the length yout want...
>
This is exactly what I intend to do, but I have no idea how to tell what
the totalLength should be for each column. This app allows users to type
in their SQL query and then execute it. The results returned are displayed
either with a DataGrid, or in a textbox where they can "copy and paste" the
results to another app or whatever. This behaviour is *EXACTLY* like
Microsoft's Query Analyzer. Their "text" output is all padded and lined up
nicely, which is what I'm trying to mimic.
Thanks for your reply...
-- Rob
Pull ThePlug to email...