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" <brian.menke@stripped>
To: "'MySQL List'" <mysql@stripped>
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
>
>
