List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:May 17 2007 1:02am
Subject:Re: Sub query help
View as plain text  
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
> 
>
Thread
Sub query helpBrian Menke17 May
  • Re: Sub query helpBrent Baisley17 May
  • Re: Sub query helpPeter Brawley17 May