Brian,
I think the answer is to create a sub query,
Without your tables I can't test this transcription, but the trick is
straightforward: if the first query includes the column(s) required to
join it correctly to the 2nd query, replace the avgscore table reference
in the second query with the entire first query, giving it the alias
avgscore:
SELECT
managers.fname,managers.lname,AVGSCORE.module_id,
ROUND(AVG(AVGSCORE.AVGSCORE) / 5 * 100) AS score
FROM stores
JOIN managers ON stores.rm_id = managers.email
JOIN (
SELECT
participants.store_id,
completed_modules.module_id,
AVG(completed_modules.score) AS AVGSCORE
FROM
participants
JOIN completed_modules ON (participants.email = completed_modules.email)
JOIN stores ON (participants.store_id = stores.store_id)
WHERE completed_modules.score IS NOT NULL
AND participants.store_id <> '10509'
GROUP BY participants.store_id, completed_modules.module_id
) AS AVGSCORE ON stores.store_id = AVGSCORE.store_id
WHERE stores.store_id <> '10509'
GROUP BY managers.lname, AVGSCORE.module_id
ORDER BY managers.lname,managers.fname,
AVGSCORE.store_id,AVGSCORE.module_id;
PB
-----
Brian Menke wrote:
MySQL Version 5.0.22
I would be thrilled if someone could even just point me to a site that
provided good examples of using sub queries. I have the following query:
SELECT
participants.store_id,
completed_modules.module_id,
AVG(completed_modules.score) AS AVGSCORE
FROM
participants
INNER JOIN completed_modules ON (participants.email =
completed_modules.email)
INNER JOIN stores ON (participants.store_id = stores.store_id)
WHERE
(completed_modules.score IS NOT NULL) AND
(participants.store_id <> '10509')
GROUP BY
participants.store_id,
completed_modules.module_id;
SELECT
managers.fname,
managers.lname,
AVGSCORE.module_id,
ROUND(AVG(AVGSCORE.AVGSCORE) / 5 * 100) AS score
FROM
stores
INNER JOIN managers ON (stores.rm_id = managers.email)
INNER JOIN AVGSCORE ON (stores.store_id = AVGSCORE.store_id)
WHERE
(stores.store_id <> '10509')
GROUP BY
managers.lname,
AVGSCORE.module_id
ORDER BY
managers.lname,
managers.fname,
AVGSCORE.store_id,
AVGSCORE.module_id;
Hopefully you can see what I am trying to do is take the results of the
first query, and run another query against those results.
This runs flawlessly in EMS SQL MANAGER and produces what I want. But of
course when I try to use it as a query in my PHP as the select statement, it
bombs. I think the answer is to create a sub query, but I can't seem to find
a good example on the web anywhere. Maybe I am just not using the right
Google "query"?
Thanks in advance for any help!
-Brian Menke
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007 10:47 AM
