CODEWITHSUNDEEP
mysqlsql
Dev01
Dev01

Reputation: 4222

SQL: Error in LIKE column for WHERE clause

I have this query:

SELECT `donations`.*, `contacts`.`first_name` AS contact_first_name, `contacts`.`last_name` AS
    contact_last_name
FROM (`donations`)
LEFT OUTER JOIN `contacts` contacts ON `contacts`.`id` =
    `donations`.`contact_id`
WHERE 
 `contact_first_name` LIKE 
    '%test%'

However I am getting error Unknow column contact_first_name in WHERE clause. I see that I am selecting first name as alias contact_first_name but still getting error.

Can anyone help as to what is wrong I am doing here ? Thanks

Upvotes: 0

Views: 41

Answers (2)

Milen
Milen

Reputation: 8867

Use this instead:

SELECT `donations`.*, `contacts`.`first_name` AS contact_first_name, `contacts`.`last_name` AS
    contact_last_name
FROM (`donations`)
LEFT OUTER JOIN `contacts` contacts ON `contacts`.`id` =
    `donations`.`contact_id`
WHERE 
 `contacts`.`first_name` LIKE 
    '%test%'

The reason for this is because contacts.first_name AS contact_first_name is being evaluated last so WHERE does not know about the alias contact_first_name Alternatively you can do:

SELECT * 
FROM (
     SELECT `donations`.*, `contacts`.`first_name` AS contact_first_name, `contacts`.`last_name` AS
           contact_last_name
     FROM (`donations`)
      LEFT OUTER JOIN `contacts` contacts ON `contacts`.`id` =
    `donations`.`contact_id`       
) a
 WHERE `contact_first_name` LIKE  '%test%'

Upvotes: 1

martincarlin87
martincarlin87

Reputation: 11042

Change 

WHERE 
  `contact_first_name` LIKE 
    '%test%'

to

WHERE 
  `contacts`.`first_name` LIKE 
    '%test%'

The reason for this is that you can't use aliases in the WHERE clause.

Upvotes: 1

Related Questions