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
