[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