I think Sol meant to copy the rest of the group on his suggestion as well...
Rhino ----- Original Message ----- From: "sol beach" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Saturday, September 25, 2004 4:01 PM Subject: Re: MIN(foo) as bar WHERE bar>50 > Something like this might get you closer. > > select p.name as parent, c.name as child > from people p, people c > where p.child = c.parent > and exists ( select min(s.age) > from people s > where s.parent = c.parent > having min(s.age) > 50); > > HTH & YMMV > > > On Sat, 25 Sep 2004 15:21:46 -0400, Rhino <[EMAIL PROTECTED]> wrote: > > > > ----- Original Message ----- > > From: "Andy Bakun" <[EMAIL PROTECTED]> > > To: "Laszlo Thoth" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Saturday, September 25, 2004 1:30 PM > > Subject: Re: MIN(foo) as bar WHERE bar>50 > > > > > On Fri, 2004-09-24 at 21:31, Laszlo Thoth wrote: > > > > Here's the kids: > > > > > > > > mysql> SELECT p.name as parent,c.name as > > > > child,(TO_DAYS(NOW())-TO_DAYS(c.dob))/365 as age FROM people as p LEFT > > JOIN > > > > people as c ON p.name=c.parent WHERE c.name IS NOT NULL ORDER BY p.dob; > > > > > > You seem to be missing a parent child relationship from your results for > > > some reason. When I run your queries, I also get a 141 year old child: > > > > > > | James Smith | Samuel P | 141.07 | > > > > > > > mysql> SELECT p.name as parent,c.name as > > > > child,MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) as minage FROM people as > > p LEFT > > > > JOIN people as c ON p.name=c.parent WHERE c.name IS NOT NULL AND minage > > > 50 > > > > GROUP BY parent ORDER BY p.dob; > > > > ERROR 1054: Unknown column 'minage' in 'where clause' > > > > > > > > Unfortunately it doesn't look like I can SELECT on the MIN() result: I > > can only > > > > specify which rows go into the MIN(). How can I perform this select? > > > > > > Look up the HAVING clause in the mysql manual. I believe HAVING is not > > > standard SQL, and is a MySQL specific addition. > > > > > > select p.name as parent, > > > c.name as child, > > > MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) as minage > > > from people as p left join > > > people as c on p.name = c.parent > > > where c.name is not null > > > group by parent > > > -> having minage > 50 > > > order by p.dob; > > > > > > I personally try to avoid using HAVING on queries where the WHERE clause > > > would return a lot of rows because it needs to look at every row to see > > > if it satisfies the HAVING condition and this can be extremely > > > inefficient. But it's useful and sometimes you can't avoid using it. > > > Your query could also be extremely inefficient because you need to apply > > > the formula to calculate minage to all rows. > > > > > > HAVING is essentially like making a temporary table and then selecting > > > from that temporary table. I could be considered a stop-gap method of > > > simple subqueries to tide us over until MySQL supports sub-queries > > > (sometime in the 5.x timeframe, I believe). > > > > > You're mistaken about HAVING being a MySQL extension on SQL. It's been part > > of DB2, my main database, for something like 20 years. I *believe* it is > > part of most of the SQL standards. (I can't prove that though because I'm > > having trouble finding the SQL standards online. Remember, there are a LOT > > of different SQL standards, like ANSI SQL, SQL92, SQL99, etc.) > > > > HAVING's job is to look at the groups that satisfy a Group By clause and to > > determine if the *groups* meet the condition specified in the HAVING clause. > > This contrasts with the WHERE clause which looks at individual rows in the > > table being queried. Sometimes, you can accomplish the same thing in a > > HAVING as in a WHERE but sometimes not. > > > > Rhino > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
