First off, thanks for taking the time to read this and try to assist.

I have 3 tables: assets,polls,aps

-Assets is a master list of all of the wireless assets in our
environment.

-Polls is an ongoing table containing data from a script that logs
into 140 AP's every hour on the hour and retrives the clients
associated at that time.

-Aps is a master list of all of the aps in our environment.


So, I am creating a report that will step through each of the assets
and find the most recent entry in the polls table, which is basically
the last time that the client was associated. (I understand that if a
client associates from :15-:45 every hour that this script will never
see it)

I am doing all of this in php. I used to have two separate queries to
do this (I've removed a lot of the other SQL and php syntax as to not
as to not garble or distract)...

#Get the list of assets
$assetlist = SELECT * FROM assets

#Start a while loop and step through each asset, find the most
#recent occurance of the asset in the polls list
$pollinfo = SELECT * FROM polls INNER JOIN aps ON
polls.ap_id=aps.ap_id WHERE polls.asset='$assetlists[0]' ORDER BY oid
DESC LIMIT 1

Can anyone help me combine these into a single query? I have been
trying many different LEFT OUTER JOINs, but I can not get the desired
results. Below is a current version of the query I am trying to
build....

SELECT * FROM assets
LEFT OUTER JOIN
(SELECT * FROM polls INNER JOIN aps ON polls.ap_id=aps.ap_id
WHERE polls.asset=assets.asset
ORDER BY oid DESC LIMIT 1)
AS pollstemp ON assets.asset=pollstemp.asset

My problem is figuring out how to get the most recent poll record
containing the current asset to join with the current record from
asset.

Thanks in advance!
-Ed

"FastEddie" <fasteddie_g (AT) yahoo (DOT) com> wrote

This is one reason to the LIMIT thing doesn't work well. Start by finding
the row that matches the MAX(oid) by whatever grouping you desire and
proceed with your joins from there.