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 accessing reportserver with web browser

I am using sql express w/ advanced services on xp pro with IIS 5.1 installed. On the local machine named 'Tester' i am able to access reportserver using a web browser and the url: http://localhost/reportserver. But when i go to another machine on the same network and domain and try to access the reportserver with url: http://Tester.domain/reportserver or http://<ip address of Tester>/reportserver ie tells me page cannot be found and does not give me any errors. How do I set up network access to reportserver on the machine Tester? Help would be greaty appreciated.

Try and turn off Windows Firewall to see if that helps.

To troubleshoot, you should be able to telnet to port 80 from the other machine.

cheers,

Andrew

Help with a confusing and advanced query - If Exsists?

Ok,

I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.

Dbo.Products – This table holds product names and information.

Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID

Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.

Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID

Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.

Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are "required" (no product will be returned that does not have what is in the list).

I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.

If any of you know how to do the above with a subquery of some kind, please let me know.

Dave LarsonI'm not sure that I catch your task properly, but try something like that:

SELECT ProductId, ProductName
FROM dbo.Products
WHERE (NOT (ProductId IN
(SELECT dbo.Products.ProductId
FROM dbo.Products INNER JOIN
dbo.Product_Attributes ON dbo.Products.ProductId = dbo.Product_Attributes.Product
WHERE (dbo.Product_Attributes.Attribute IN
(SELECT dbo.Castomer_Attributes.Attribute
FROM dbo.Castomer_Attributes INNER JOIN
dbo.Customers ON dbo.Castomer_Attributes.Customer = dbo.Customers.CustomerId
WHERE (dbo.Customers.CustomerId = @.CustomerId))))))

where @.CustomerId is a parameter.