You can't use a value calculated in the SELECT clause as a constraint in
the WHERE clause (its a chicken & egg problem); the WHERE clause is what
determines the values in the SELECT clause. What you want is the HAVING
clause which is applied *after* all matching rows have been found:
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;
Garth
On Fri, 2004-09-24 at 19:31, Laszlo Thoth wrote:
> I'm trying to construct a query and running into either a limitation of the SQL
> language or (more probably) a limitation in my *comprehension* of the SQL
> language. Here's a simplified version of my data set:
>
> =======================================================
> CREATE TABLE people (
> name varchar(11) default NULL,
> parent varchar(11) default NULL,
> dob date default NULL
> );
>
> INSERT INTO people VALUES ('George W','George HW','1946-07-06');
> INSERT INTO people VALUES ('George HW','Prescott','1924-06-12');
> INSERT INTO people VALUES ('Prescott','Samuel P','1895-05-15');
> INSERT INTO people VALUES ('Samuel P','James Smith','1863-10-04');
> INSERT INTO people VALUES ('Jeb','George HW','1953-02-11');
> INSERT INTO people VALUES ('Neil','George HW','1955-01-22');
> INSERT INTO people VALUES ('Marvin','George HW','1956-10-22');
> INSERT INTO people VALUES ('Jenna','George W','1981-11-25');
> INSERT INTO people VALUES ('Barbara','George W','1981-11-25');
> INSERT INTO people VALUES ('James Smith','Obadiah','1825-06-15');
> =======================================================
>
> THE PROBLEM: I want to select parents whose children are all more than 50 years
> old. (If a parent has one 55 year old and one 45 year old they don't get
> selected: having children under 50 disqualifies them from selection.)
>
> 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;
> +-----------+-----------+--------+
> | parent | child | age |
> +-----------+-----------+--------+
> | Samuel P | Prescott | 109.44 |
> | Prescott | George HW | 80.34 |
> | George HW | George W | 58.26 |
> | George HW | Jeb | 51.65 |
> | George HW | Neil | 49.71 |
> | George HW | Marvin | 47.96 |
> | George W | Jenna | 22.85 |
> | George W | Barbara | 22.85 |
> +-----------+-----------+--------+
> 8 rows in set (0.00 sec)
>
> Here's the youngest kids:
>
> 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 GROUP BY parent
> ORDER BY p.dob;
> +-----------+-----------+--------+
> | parent | child | minage |
> +-----------+-----------+--------+
> | Samuel P | Prescott | 109.44 |
> | Prescott | George HW | 80.34 |
> | George HW | George W | 47.96 |
> | George W | Jenna | 22.85 |
> +-----------+-----------+--------+
> 4 rows in set (0.01 sec)
>
> And here's my attempted solution:
>
> 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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
