SQL statement with 3 tables and 1 MAX date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL statement with 3 tables and 1 MAX date

Author

Topic

lotsill
Starting Member

4 Posts

Posted - 2008-10-27 : 16:06:29

Hello,

I've been trying to create a query that pulls data from 3 tables with only 1 table containing dates
I'm unable to figure out for the life of me where the MAX function should go with 3 tables.

The JOINS are on "_ResourceGuid"

The max value I need is from Evt_NS_Client_Config_Request.StartTime

I've included a set of sample data below the query.

SELECT     Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version], vComputer.Name, 
                      Evt_NS_Client_Config_Request.StartTime
FROM         Evt_NS_Client_Config_Request 
INNER JOIN  Inv_AeX_AC_Client_Agent ON Evt_NS_Client_Config_Request._ResourceGuid = Inv_AeX_AC_Client_Agent._ResourceGuid 
INNER JOIN vComputer ON Inv_AeX_AC_Client_Agent._ResourceGuid = vComputer.Guid
WHERE     (Inv_AeX_AC_Client_Agent.[Product Version] < N'6.1.1') AND (Inv_AeX_AC_Client_Agent.[Agent Name] = N'Inventory Agent Package')

SAMPLE DATA

Sample Data


Inv_AeX_AC_Client_Agent

Agent name        		Product Version   	      _ResourceGuid

Inventory Agent Package			6.1				1
Software Agent				5.5				1
Update Agent				5.7				1
Inventory Agent Package			6.1				2
Software Agent				5.5				2
Update Agent				5.7				2



VComputer

Name			Guid

Bob			1
Mike			2
Sumit			3	



Evt_NS_Client_Config_Request


StartTime		_ResourceGuid

	
12:00				1
12:15				1
12:30				1
12:07				2
12:15				2
12:30				2

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 16:31:23

[code]SELECT Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version], vComputer.Name,
MAX(Evt_NS_Client_Config_Request.StartTime)
FROM Evt_NS_Client_Config_Request
INNER JOIN Inv_AeX_AC_Client_Agent ON Evt_NS_Client_Config_Request._ResourceGuid = Inv_AeX_AC_Client_Agent._ResourceGuid
INNER JOIN vComputer ON Inv_AeX_AC_Client_Agent._ResourceGuid = vComputer.Guid
WHERE (Inv_AeX_AC_Client_Agent.[Product Version] < N'6.1.1') AND (Inv_AeX_AC_Client_Agent.[Agent Name] = N'Inventory Agent Package')
group by Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version], vComputer.Name
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 00:04:50

[code]SELECT Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version], vComputer.Name,
e.lateststarttimeFROM (SELECT _ResourceGuid,MAX(StartTime) AS lateststarttime
FROM Evt_NS_Client_Config_Request
GROUP BY _ResourceGuid)e
INNER JOIN Inv_AeX_AC_Client_Agent ON e._ResourceGuid = Inv_AeX_AC_Client_Agent._ResourceGuid INNER JOIN vComputer ON Inv_AeX_AC_Client_Agent._ResourceGuid = vComputer.Guid
WHERE (Inv_AeX_AC_Client_Agent.[Product Version] < N'6.1.1') AND (Inv_AeX_AC_Client_Agent.[Agent Name] = N'Inventory Agent Package') [/code]

lotsill
Starting Member

4 Posts

Posted - 2008-10-28 : 10:48:10

hanbingl/visakh16,

When I run these queries, I get no results.

I've included my original query which will returns results with product agent name and product version but I need to obtain the most recent "startime" from the "Evt_NS_Client_Config_Request".

Need to add MAX(Evt_NS_Client_Config_Request.StartTime)to the query. I just don't know where how to add the MAX function between 2 or more tables.

Thanks

quote:

SELECT Inv_AeX_AC_Client_Agent._ResourceGuid, Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version], vComputer.[Name]
FROM Inv_AeX_AC_Client_Agent INNER JOIN
vComputer ON Inv_AeX_AC_Client_Agent._ResourceGuid = vComputer.Guid
WHERE (Inv_AeX_AC_Client_Agent.[Agent Name] = N'Inventory Agent Package') AND (Inv_AeX_AC_Client_Agent.[Product Version] < N'6.1.1')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:01:40

did you get any error? or is it that no results were returned?

lotsill
Starting Member

4 Posts

Posted - 2008-10-28 : 11:06:13

No results were returned, I believe the problem may have been the INNER JOINS between the 3 tables.

Query that produced no results

quote:
SELECT Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version], vComputer.Name,
e.lateststarttime
FROM (SELECT _ResourceGuid,MAX(StartTime) AS lateststarttime
FROM Evt_NS_Client_Config_Request
GROUP BY _ResourceGuid)e
INNER JOIN Inv_AeX_AC_Client_Agent ON e._ResourceGuid = Inv_AeX_AC_Client_Agent._ResourceGuid INNER JOIN vComputer ON Inv_AeX_AC_Client_Agent._ResourceGuid = vComputer.Guid
WHERE (Inv_AeX_AC_Client_Agent.[Product Version] < N'6.1.1') AND (Inv_AeX_AC_Client_Agent.[Agent Name] = N'Inventory Agent Package')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:09:34

are you sure you used the exact query i posted? also is your data exactly as you posted?

lotsill
Starting Member

4 Posts

Posted - 2008-10-28 : 16:19:08

I copied the entire query and it failed. So I added a space between "e.lateststarttime" and "FROM" that returned no results.

Here is a query that will returns the MAX time

How do I amend the query to include the following, joining of the [resourceguid]

SELECT Inv_AeX_AC_Client_Agent.[Agent Name], Inv_AeX_AC_Client_Agent.[Product Version]
FROM Inv_AeX_AC_Client_Agent
WHERE (Inv_AeX_AC_Client_Agent.[Product Version] < N'6.1.1') AND (Inv_AeX_AC_Client_Agent.[Agent Name] = N'Inventory Agent Package')

select i.[_resourceguid], i.[name] as 'Computer Name',
e.[_eventTime] as 'Last Recorded Configuration Request'
from
Inv_AeX_AC_Identification i
inner join evt_ns_client_config_request e
on i.[_resourceguid]=e.[resourceguid]
WHERE e.[_id] in
(select MAX([_id]) from evt_ns_client_config_request group by [resourceguid])
order by e.[_eventTime] ASC

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 17:03:01

Our queries will work if the tables and the sample data provided in OP matches yours.
Please provide us with real tables and some sample data.

What is _ID, _eventTime ? I don't think MAX(_id) means max(StartTime).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources