----- Original Message -----
From: "Brett Harvey" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Saturday, November 26, 2005 9:45 PM
Subject: Re: SQL HAVING statement ?
"Rhino" <[EMAIL PROTECTED]> wrote:
Second, you're using the HAVING clause incorrectly in your query.
HAVING only works with GROUP BY, i.e. you MUST have a GROUP BY to be
able to use HAVING. (Note: You can have a GROUP BY without using
HAVING but you cannot use HAVING unless a GROUP BY is present.)
Since you have no GROUP BY, there is no way that this query will
ever work.
I disagree.
SELECT CONCAT(people2.First_Name, ' ', people2.Last_Name) AS zNAME,
events.name_short,
events.date_start
FROM people2 INNER JOIN event_people ON people2.ID =
event_people.peopleID
INNER JOIN events ON event_people.eventID = events.ID
WHERE event_people.people_role = 'FACULTY'
AND zNAME LIKE 'XXXXXXXX%'
ORDER BY events.date_start DESC
This wouldn't work.
select userfirstnm, userlastnm, concat(userfirstnm, ' ',userlastnm)
as zname from FMS.WebUsers_sql where zname Like "%brett%"
results in Unknown column 'zname' in 'where clause'. There "where"
must be on the real column name. "Standard SQL doesn't allow you to
refer to a column alias in a WHERE clause. This is because when the
WHERE code is executed, the column value may not yet be determined. "
http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html
Okay, I admit I was crossing my fingers a bit with that answer: I assumed
that the alias would work in the WHERE; apparently, I was overly optimistic.
However,
select userfirstnm, userlastnm, concat(userfirstnm,' ',userlastnm)
as zname from FMS.WebUsers_sql having zname Like "%brett%"
works.
Having must come after any grouping, but a group by is not required.
Per the documentation
"A HAVING clause can refer to any column or alias named in a
select_expr in the SELECT list or in outer subqueries, and to
aggregate functions. (Standard SQL requires that HAVING must
reference only columns in the GROUP BY clause or columns used in
aggregate functions.)"
The concat is the aggregate function. However, it works on just
aliases also. SELECT userfirstnm as first, userlastnm, from
FMS.WebUsers_sql having first Like "%brett%"
Okay, once again I stand corrected. In my own defense, I should say that I
am primarily a DB2 user and only use MySQL sporadically. In 20 years of
working with DB2, I have never seen a query with a HAVING but no GROUP BY
work. Since MySQL behaves like DB2 in virtually every case I've seen, I just
assumed that rule also applied to MySQL. Apparently, I'm wrong in this case.
What made this work was simply using the % he had forgotten
SELECT CONCAT(people2.First_Name, " ", people2.Last_Name) AS zNAME,
events.name_short,
events.date_start
FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID
INNER JOIN events ON event_people.eventID = events.ID
WHERE event_people.people_role = "FACULTY"
HAVING zNAME LIKE "%XXXXXXXX%"
ORDER BY events.date_start DESC
Well, at least I got _that_ right ;-)
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
