List:General Discussion« Previous MessageNext Message »
From:Brian Menke Date:May 16 2007 11:25pm
Subject:Sub query help
View as plain text  
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


Thread
Sub query helpBrian Menke17 May
  • Re: Sub query helpBrent Baisley17 May
  • Re: Sub query helpPeter Brawley17 May