In a nutshell, one way to do subqueries is to just name the query and join on
it as if it was a regular table.
SELECT field1,field2,... FROM table1
INNER JOIN table2 ON field1=fieldT2
INNER JOIN (SELECT fieldA, fieldB FROM tableA WHERE ...) AS table3
ON fieldA=field1
...
More commonly people seemed to IN subquery, but I find it doesn't scale as
well. At least for what I do.
SELECT field1,field2 FROM table1 WHERE field1 IN (SELECT fieldA,fieldB FROM
tableA WHERE ...)..
----- Original Message -----
From: "Brian Menke" <[EMAIL PROTECTED]>
To: "'MySQL List'" <mysql@lists.mysql.com>
Sent: Wednesday, May 16, 2007 7:25 PM
Subject: Sub query help
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
