[SOLVED] multiple table update and sorting

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

[SOLVED] multiple table update and sorting

Post 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)
Last edited by ryuuka on Mon Sep 11, 2006 6:20 am, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

yes

and it needs to display only the records that are in table 1 where the controleren is 1
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post 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
Post Reply