What version of MySQL are you running? Version 4.1.x and 5.0.x offer many
more potential solutions than the earlier versions, which don't support
subqueries.
Rhino
----- Original Message -----
From: "Laszlo Thoth" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, September 24, 2004 10:31 PM
Subject: MIN(foo) as bar WHERE bar>50
> 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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
