List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 19 2005 6:21pm
Subject:Re: Inner Join & Search on Two (2) fields ?
View as plain text  
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

Thread
Inner Join & Search on Two (2) fields ?m i l e s19 Nov
  • Re: Inner Join & Search on Two (2) fields ?Michael Stassen19 Nov
    • Re: Inner Join & Search on Two (2) fields ?m i l e s19 Nov
  • Re: Inner Join & Search on Two (2) fields ?Peter Brawley19 Nov