----- Original Message -----
From: "m i l e s" <[EMAIL PROTECTED]>
To: "MySQL" <mysql@lists.mysql.com>
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 "XXXXXXXX"
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.
EMPNO FIRSTNME LASTNAME WORKDEPT SEX SALARY
1 Bob Smith A00 M
50000.00
2 Mary Jones B01 F
55000.000
3 Fred Green B01 M
48000.00
4 Edna Murphy A00 F
51000.00
5 James Willis B01 M
35000.00
6 Ingrid Steele C01 F
62000.00
This query would display all of the rows of the table:
select empno, lastname, workdept, sex, salary
from employee
EMPNO FIRSTNME LASTNAME WORKDEPT SEX SALARY
1 Bob Smith A00 M
50000.00
2 Mary Jones B01 F
55000.000
3 Fred Green B01 M
48000.00
4 Edna Murphy A00 F
51000.00
5 James Willis B01 M
35000.00
6 Ingrid Steele C01 F
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
WORKDEPT SUM_SALARY
A00 101000.00
B01 138000.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
120000.00. Here's the query:
select workdept, sum(salary) as sum_salary
from employee
where workdept <> 'C01'
group by workdept
having sum(salary) > 120000
WORKDEPT SUM_SALARY
B01 138000.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 summarization and that you don't need to use HAVING (or GROUP BY)
in the first place. Therefore, file your new knowledge about GROUP BY and
HAVING away for future reference - you'll need them again some day - and
let's fix your original query.
You are trying to do some kind of search on a concatenation of first name
and last name. You obviously know about the concat() function so let's use
it correctly. Assuming you need the concatenated name to be displayed in the
result set, you need something like this:
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
Note that I've used single quotes where you used double quotes; your
approach may work okay in MySQL but I believe the SQL standard is to use
single quotes. The other thing I've done is to add a wild card character to
the expression you use in your LIKE clause; a LIKE clause implies that you
are searching for something based on a pattern and a pattern always involves
wild cards. (If you don't want a pattern, then just use an = operator
instead of LIKE.)
The main change I made was to replace 'HAVING' with 'AND'; the revised query
simply has two search conditions, connected by 'AND' in its WHERE clause.
Try this and see what happens.
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]
