[thelist] MySQL 3.x subqueries
Anthony Baratta
Anthony at Baratta.com
Fri Jan 2 22:27:18 CST 2004
At 08:13 PM 1/2/2004, David Siedband wrote:
>I have two SQL queries that I'm trying to combine using subqueries.
First off, I don't think sub-queries are supported with mySQL. What I think
you want to do is use the Group By clause.
>select distinct Hypoth.ID , Hypoth.ShortName , Hypoth.Priority (select
>avg(Priority) as Priority from HypImpact where HypID = Hypoth.ID)
>from Hypoth , HypSubCats
>where HypSubCats.SubCatID = <dtml-sqlvar SubCatID type=int>
>and HypSubCats.HypID = Hypoth.ID
>and Hypoth.ShortName !='';
This is my first whack at it:
select
Hypoth.ID
, Hypoth.ShortName
, avg(HypImpact.Priority) as Priority
from Hypoth
join HypSubCats on HypSubCats.HypID = Hypoth.ID
join HypImpact on HypImpact.HypID = Hypoth.ID
where HypSubCats.SubCatID = <dtml-sqlvar SubCatID type=int>
and Hypoth.ShortName !=''
group by Hypoth.ID, Hypoth.ShortName
---
Anthony Baratta
President
Keyboard Jockeys
"Conformity is the refuge of the unimaginative."
More information about the thelist
mailing list