From: Peter Brawley
Date: May 17 2007 1:03am
Subject: Re: Sub query help
List-Archive: http://lists.mysql.com/mysql/206839
Message-Id: <464BA9DA.5070000@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------030506030409020401000700"

--------------030506030409020401000700
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>   

--------------030506030409020401000700--