From: fasteddie_g@yahoo.com
To: pgsql-sql@postgresql.org
Subject: SQL / Join Help
Date: 18 Feb 2004 13:15:07 -0800
Message-ID: <8d6215a0.0402181315.1534ac0@posting.google.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fasteddie_g@yahoo.com
Subject: Re: SQL / Join Help
In-Reply-To: <8d6215a0.0402181315.1534ac0@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox