[vox-tech] MySQL 3.x subqueries question

[David Siedband]

I have two SQL queries that I'm trying to combine using subqueries.

These are part of a web app lets people vote on the relative importance 
of a bunch of hypotheses

The one that will be the parent query gets all the hypotheses in a 
selected category.  The query that I'd like to make the subquery looks 
up all the priority ratings that people have entered and averages them. 
  I'd like to combine them so that I can sort by this averaged 
importance value.

FWIW, I'm using MySQL 3.23

[Parent]
select distinct Hypoth.ID , Hypoth.ShortName , Hypoth.Priority
from Hypoth , HypSubCats
where HypSubCats.SubCatID = <dtml-sqlvar SubCatID type=int>
and HypSubCats.HypID = Hypoth.ID
and Hypoth.ShortName !='';

[child]
select avg(Priority) as Priority
from HypImpact
where HypID = <dtml-sqlvar hid type=int>;

(note: the hid variable is returned by the first query)

Here is the concept for the combined query:

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 !='';

Can anyone point me in the right direction?

Thanks!
--
David