"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
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%"
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
--
--------------------------------------------------------------------------------
/Brett C. Harvey;
/Creative-Pages.Net, President;
/Facility Management Systems, CTO (www.fmsystems.biz);
/Lasso Partner Association Member ID #LPA135259
(www.omnipilot.com/www.lassopartner.com);
--------------------------------------------------------------------------------
