SubQuery in MySQL ??

[admin]

Hi there,

I have a problem creating a Select statement to extract the correct info out of a DB. I have two tables which hold info on servers that I want to monitor. Here are the relevant tables :

TABLE SERVER
ServerID
Hostname
IP_Address
Mac_Address

TABLE DISKSPACE
ServerID
PartitionID
Time
Total
Free

I want to view the used diskspace per server and per partition (C:, D:, ...)

This is what I've come up with :

SELECT hostname, partid, max(time), total, free, (total - free) AS used
FROM diskspace d, server s
WHERE s.servid=d.servid
AND hostname=\"$host\"
GROUP BY hostname, partid

The TIME field in the Diskspace table is a TIMESTAMP type, so it gets updated on each insert with a YYYYMMDDHHMMSS value. Max(Time) selects the largest timevalue, ie. the morst recent entry.

Running this query gives me a strange effect : I get the values associated with the first entry in time, but the value in the timefield displays the last time. Any ideas ?

I tried doing it using a subquerie, along the lines of the following :

SELECT hostname, partid, time, total, free, (total - free) AS used
FROM diskspace d, server s
WHERE s.servid=d.servid
AND hostname=\"$host\"
AND time=(
SELECT max(time) FROM diskspace d, server s
WHERE s.servid=d.servid
AND hostname=\"$host\"
GROUP BY hostname, partid)
GROUP BY hostname, partid

But unfortunately, MySQL doesn't support subqueries .

So could someone rewrite this as a single query ?

Also, I've been trying to create a nice history of diskspace usage, but I've not thought that one out very much. Any ideas are welcome !


Thanks a lot !

Joris.