On Fri, Jan 02, 2004 at 08:12:27PM -0800, David Siedband wrote: > I have two SQL queries that I'm trying to combine using subqueries. [snip] > FWIW, I'm using MySQL 3.23
You need at least MySQL 4.1 for subqueries. > [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 !=''; Is Priority a column in your Hypoth table? Your scalar subquery should probably be written like this: select Hypoth.ID, Hypoth.ShortName, (select avg(Priority) from HypImpact where HypID = Hypoth.ID) as Priority from ... But you don't need to use a subquery here. I think the query can be rewritten as follows: select Hypoth.ID, Hypoth.ShortName, avg(HypImpact.Priority) as Priority from Hypoth left join HypSubCats on HypSubCats.HypID = Hypoth.ID left join HypImpact on HypImpact.HypID = Hypoth.ID where HypSubCats.SubCatID = <whatever> and Hypoth.ShortName != '' group by Hypoth.ID, Hypoth.ShortName order by Priority -David _______________________________________________ vox-tech mailing list [EMAIL PROTECTED] http://lists.lugod.org/mailman/listinfo/vox-tech
