Re: SQL HAVING statement ?
- Original Message - From: "Brett Harvey" <[EMAIL PROTECTED]> To: 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 '%' 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 "%%" 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]
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 '%' 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 "%%" 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);
Re: SQL HAVING statement ?
Rhino, Thanks for all the info. It was as simple as %% in the statement... DUH! I can't believe I missed that. Sorry to have bothered. Thanks anyway. M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL HAVING statement ?
- Original Message - From: "m i l e s" <[EMAIL PROTECTED]> To: "MySQL" Sent: Saturday, November 26, 2005 6:39 PM Subject: SQL HAVING statement ? Hi, Im having a bit of a problem with the following query: 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 "" ORDER BY events.date_start DESC Specifically, the HAVING statement Im trying to perform a search against the concatenated result of first_name/last_name and not getting very far. Can anyone point me in the right direction ??? First of all, when posting a question like this it is very hard to help if you don't supply a specific error message - if there is one! - or at least describe what is wrong with the answer you are getting, i.e. how it differs from what you wanted. 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. Now, before you try to add a GROUP BY, I don't think you need GROUP BY _or_ HAVING to fix your query! From the nature of your question, I'm going to assume that you are quite new to SQL; please forgive me if I'm wrong. In a nutshell, GROUP BY is used to cause summaries of data and HAVING is used much like a WHERE clause but for the _groups_ formed by GROUP BY. I don't think you need either one. Here are a few quick examples of GROUP BY and HAVING to illustrate their use. Imagine a table that contains employee records, with one row for each employee. Each row contains an employee number, a last name, a first name, the number of the department for which the employee works, the employee's sex and annual salary. EMPNOFIRSTNMELASTNAMEWORKDEPTSEXSALARY 1Bob SmithA00M 5.00 2Mary JonesB01F 55000.000 3Fred GreenB01M 48000.00 4EdnaMurphy A00F 51000.00 5James Willis B01M 35000.00 6IngridSteele C01F 62000.00 This query would display all of the rows of the table: select empno, lastname, workdept, sex, salary from employee EMPNOFIRSTNMELASTNAMEWORKDEPTSEXSALARY 1Bob SmithA00M 5.00 2Mary JonesB01F 55000.000 3Fred GreenB01M 48000.00 4EdnaMurphy A00F 51000.00 5James Willis B01M 35000.00 6IngridSteele C01F 62000.00 This query would report the total salary paid out to all people in each department but would omit department C01: select workdept, sum(salary) as sum_salary from employee where workdept <> 'C01' group by workdept WORKDEPTSUM_SALARY A00101000.00 B01138000.00 See? The query is doing a summarization: instead of showing the details of each individual in each department, we show only the total salary paid to the people in the department and the department number. (If we omitted the department number from the SELECT clause but left it in the GROUP BY clause, the query would work but would show only the SUM_SALARY column; we wouldn't know which department was associated with each sum, making the result pretty useless.) Now, let's add a HAVING. Suppose we only wanted to show a department in the result set if the total salary paid out to the department exceeded 12.00. Here's the query: select workdept, sum(salary) as sum_salary from employee where workdept <> 'C01' group by workdept having sum(salary) > 12 WORKDEPTSUM_SALARY B01138000.00 The HAVING is similar in concept to the WHERE but it applies to the rows that result from the GROUP BY. This query does the exact same work as the previous query but, this time, an additional step is done: MySQL looks at the result so far, then applies the HAVING and realizes that the row for department A00 needs to be omitted from the final result. Okay, if you followed me this far, I think you'll agree that you're not doing any summar
SQL HAVING statement ?
Hi, Im having a bit of a problem with the following query: 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 "" ORDER BY events.date_start DESC Specifically, the HAVING statement Im trying to perform a search against the concatenated result of first_name/last_name and not getting very far. Can anyone point me in the right direction ??? M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
