Page 1 of 1

[SOLVED] multiple table update and sorting

Posted: Mon Sep 11, 2006 2:18 am
by ryuuka
k back again

here's my problem i need to make a site in wich services are registered and checked for updates.
for this purpose i made 2 tables 1 table has the names of the srvices that need to be updated and the
other is updated autmaticly. i need a query wich checks the first table for the services that need to be checked
and then get the latest update from the second table.

example:
table 1
has 3 services

table 2 has 5 services with about 100 records.

i need to display only the last update from the 3 services mentioned in the first table.

full code i have:

Code: Select all

SELECT     dbo.ryuuka_services_status.servernaam, dbo.ryuuka_services_status.servicenaam, dbo.ryuuka_services_status.status, 
                      MAX(dbo.ryuuka_services_status.lastcheck) AS dbo.ryuuka_services_status.lastcheck
FROM         dbo.ryuuka_services_status INNER JOIN
                          (SELECT     TOP 9999 dbo.ryuuka_services_status.servernaam, MAX(dbo.ryuuka_services_status.lastcheck),
                                                   AS dbo.ryuuka_services_status.lastcheck, dbo.ryuuka_services_status.status, dbo.ryuuka_services_status.servicenaam
                            FROM          ryuuka_log_Services A
                            WHERE      (dbo.ryuuka_services_status.lastcheck =
                                                       (SELECT     TOP 1 dbo.ryuuka_services_status.lastcheck, dbo.ryuuka_services_status.servicenaam
                                                         FROM          ryuuka_services_status
                                                         WHERE      (dbo.ryuuka_log_Services.controleren = '1') AND 
                                                                                (dbo.ryuuka_log_Services.servicenaam LIKE dbo.ryuuka_services_status.servicenaam))
                            GROUP BY dbo.ryuuka_services_status.servernaam, dbo.ryuuka_services_status.servicenaam, dbo.gispen_services_status.status
                            ORDER BY dbo.ryuuka_services_status.servernaam, dbo.ryuuka_services_status.lastcheck DESC) DERIVEDTBL
GROUP BY dbo.ryuuka_services_status.servernaam, dbo.ryuuka_services_status.lastcheck, dbo.ryuuka_services_status.status, 
                      dbo.ryuuka_services_status.servicenaam

that was the full code i got i have a simpler one 2 but that one only displays the last update that was made and not the needed updates.

Code: Select all

SELECT ryuuka_services_status.servernaam, ryuuka_services_status.servicenaam, ryuuka_services_status.status, ryuuka_services_status.lastcheck
                      FROM  ryuuka_services_status INNER JOIN 
                      ryuuka_log_Services ON ryuuka_services_status.servicenaam = ryuuka_log_Services.servicenaam
                      WHERE     (ryuuka_log_Services.controleren = '1') AND (ryuuka_log_Services.servicenaam LIKE ryuuka_services_status.servicenaam)

Posted: Mon Sep 11, 2006 3:27 am
by volka
This query is far too long an unstructured or me to read through.

What's the criteria for records in table 1?
And what's the criteria for joining records in table 2?

Posted: Mon Sep 11, 2006 3:41 am
by ryuuka
table 1
computernaam <- name of the server
displaynaam <- display name of the service
servicenaam <- actual name of the service
status <- current status
datum <- date the service was added
controleren <- if this is 1 the service needs to be checked if it's 0 it doesn't need to be checked


table 2
servernaam <- name of the server
servicenaam <- name of the service
status <- current status
lastcheck <- when the service was checked last


what i need for this to do is display the second table if the servicenaam from table 1 is in it and if it has a 1 in controleren. And since it's checked every 5 min i need it only to display the latest update.

Posted: Mon Sep 11, 2006 3:59 am
by volka
ryuuka wrote:And since it's checked every 5 min i need it only to display the latest update.
And that's simply the record with the highest/latest value in lastcheck?

Posted: Mon Sep 11, 2006 4:01 am
by ryuuka
yes

and it needs to display only the records that are in table 1 where the controleren is 1

Posted: Mon Sep 11, 2006 4:12 am
by volka
Not sure wether it really covers your problem, but try

Code: Select all

SELECT
	a.computernaam,a.displaynaam,a.servicenaam,a.status,a.datum,a.controleren,
	b.servernaam,b.servicenaam,b.status,b.lastcheck
FROM
	table1 as a
LEFT JOIN
	table2 as b
ON
	a.computernaam=b.servernaam AND a.servicenaam=b.servicenaam
LEFT JOIN
	table2 as c
ON
	b.computernaam=c.servernaam AND b.servicenaam=c.servicenaam
	AND b.lastcheck<c.lastcheck
WHERE
	a.controleren=1
	AND c.lastcheck IS Null

Posted: Mon Sep 11, 2006 6:20 am
by ryuuka
thanks man it works now

one little mistake though

Code: Select all

ON 
        b.computernaam
this is supposed to be b.servernaam

thanks a lot