Re: Inner Join & Search on Two (2) fields ?

[Peter Brawley]

Miles,

>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 ?

Nope, you can't reference a column alias ('zname') in a WHERE clause.

PB

-

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.





--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 11/18/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Inner Join & Search on Two (2) fields ?

[m i l e s]

Michael,

My apologies for not formatting the request properly for readability.

I was typing fast...

THanks for the tip...I'll try it out

Most Sincerely,

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: Inner Join & Search on Two (2) fields ?

[Michael Stassen]

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]

Inner Join & Search on Two (2) fields ?

[m i l e s]

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.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]