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).
A more efficient query might be something like this pseudo-code:
all rows where max(dob) < date_sub(now(), interval 50 years)
which should give you similar results... the query is going to look
something like this:
select p.name as parent,
max(c.dob) as dob_of_youngest_child
from people p left join
people c on p.name = c.parent
where c.name is not null
group by parent
having dob_of_youngest_child < date_sub(now(), interval 50 year)
This may end up being more efficient because the values being applied to
max() are constant values (from the table, and are not the result of a
formula) and "date_sub(now(), interval 50 year)" is also a constant
(calculated at query parse time and not for every row, and optimized
away).
Use the EXPLAIN syntax to check for efficiency.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
