On Sat, 2004-09-25 at 00:35, Søren Ragsdale wrote:
> Whoah. HAVING. I have never heard of that keyword before, possibly
> because it is not listed in the MySQL manual TOC. Thank you very much!
Its not in the TOC but its part of the SELECT statement docs:
http://dev.mysql.com/doc/mysql/en/SELECT.html
Other select clauses enjoy direct reference from the TOC because they
have more complications than HAVING.
> Out of general "there's more than one way to skin a cat" interest, is
> there another way to efficiently do this if you *didn't* have a
> "HAVING" keyword? I was trying to kludge something together out of
> UNION or DISTINCT statements or temporary tables and getting totally
> flustered.
As far as I know HAVING is ANSI SQL. Its its not, every DB I've looked
at has it.
Looking at the original post, I noticed that I didn't really pay
attention to the query itself :(. Adding HAVING doesn't solve the
problem. Unfortunately, you'll either need sub queries or use a
temporary table.
Garth
> On Sep 24, 2004, at 8:53 PM, Garth Winter Webb wrote:
>
> > 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?
> >
>
>
> !DSPAM:41551fd5107421494751132!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
