m i l e s wrote:
SELECT events.name_short, events.type, events.location,
events.description, events.faculty, concat(people2.First_Name,
people2.Last_Name) as zName, people2.roles
FROM events INNER JOIN people2 ON events.faculty = people2.id
WHERE people2.roles = "faculty" AND zName = "some name"
Is this SELECT statement correct ?
M i l e s.
I expect you got an error. It's always a good idea to include the error message
so we have something to go on. It's also a good idea to reformat your query to
make it easier to read, so we don't have to work so hard to help you. Here's
one suggested reformatting:
SELECT events.name_short, events.type, events.location,
events.description, events.faculty,
CONCAT(people2.First_Name, people2.Last_Name) as zName,
people2.roles
FROM events
INNER JOIN people2 ON events.faculty = people2.id
WHERE people2.roles = "faculty"
AND zName = "some name";
The problem is the last WHERE condition. You can't use an alias in a WHERE
clause. The WHERE clause comes before calculating values and assigning aliases,
so zName makes no sense there. You need to move that condition to the HAVING
clause, which filters rows in the end, after all calculations, like this:
SELECT events.name_short, events.type, events.location,
events.description, events.faculty,
CONCAT(people2.First_Name, people2.Last_Name) as zName,
people2.roles
FROM events
INNER JOIN people2 ON events.faculty = people2.id
WHERE people2.roles = "faculty"
HAVING zName = "some name";
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
