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]
