----- 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]
