[SOLVED] Alias searching

[lordzardeck]

I was wondering how you would search an alias column. For example, I want to search a name with two CONCAT fields as name. Here's what I'm trying, but it doesn't show up with anything even though I know the name is there:

 

SELECT
CONCAT(patron.firstname, " ", patron.lastname) AS name
FROM
patron
WHERE
'name' = "*******"

[Daniel0]

You need to do

WHERE name = '*******'

 

Keywords should be without quotes or with backticks (`). String literals should be with single quotes.

[lordzardeck]

actually it's supposed to be 'name' LIKE '%templeton%' where templeton is a last name

[lordzardeck]

I solved it. I had to concat the fields I wanted to search as follows:

 

SELECT
CONCAT(patron.firstname, " ", patron.lastname) AS name
FROM
patron
WHERE CONCAT(patron.firstname, " ", patron.lastname) = '%templeton%'

[Daniel0]

actually it's supposed to be 'name' LIKE '%templeton%' where templeton is a last name

 

No, you need to drop the single quotes around the identifier (name). That's why it doesn't work for you. It's like checking whether the word dog equals the word cat.

[lordzardeck]

oh i see. well how do you reference an alias column, it always set unknown column name

[Daniel0]

SELECT CONCAT(patron.firstname, ' ', patron.lastname) AS name
  FROM patron
  WHERE name = '*******';

 

name is an identifier. Those do not have single nor double quotes around them in SQL. Otherwise they are string literals.

 

If you do e.g. 'name' = 'foo' then you are selecting all rows from patron where it is true that the string name is the same as the string foo. This is never true and as such you will never get any results.

 

If you do name = 'foo' then you are selecting all rows from patron where it is true that the column[/tt] (or alias) called name equals the string foo. This may or may not be true for some rows, but for those rows where that statement is true will be returned, the others won't.