Unknown Column In Where Clause With Join

1

votes

I want to join 3 table one column sum has to be compare with another column

Here is my query

SELECT 
    *, `e`.`id` AS `event_ac_id`, 
    SUM(CASE WHEN trans.gift_transaction_status = 1 
               THEN trans.event_gift_amount  
               ELSE 0 END) as amount 
FROM 
    `tbl_event_category` `cat` 
LEFT JOIN 
    `tbl_event` `e` ON e.event_category = cat.id 
LEFT JOIN 
    `tbl_organisation` `org` ON e.organisation_id = org.id 
LEFT JOIN 
    `tbl_event_gift_transaction` `trans` ON e.id = trans.event_id 
WHERE 
    cat.type ='campaign' AND is_approved=1
    AND e.funding_goal_amount <= amount 
GROUP BY 
    `event_ac_id` 
LIMIT 8

Exception (Database Exception) 'yii\db\Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'amount' in 'where clause'

mysqlsqlyii

2

votes

A computed value cannot be on the WHERE clause. Only actual columns need to be used there.

If you want to filter AFTERWARDS the WHERE clause has been executed, you can use the HAVING statement

2

votes

You need to understand that SQL is evaluated from right to left. So when the MYSQL parser looks for amount column then it is not found and hence the error.

The manual says:

"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses."

and

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

You can try this:

SELECT *, `e`.`id` AS `event_ac_id`, 
   SUM(CASE WHEN trans.gift_transaction_status = 1 THEN trans.event_gift_amount  
   ELSE 0 END) as amount 
FROM `tbl_event_category` `cat` LEFT JOIN `tbl_event` `e` 
       ON e.event_category=cat.id 
LEFT JOIN `tbl_organisation` `org` ON e.organisation_id=org.id 
LEFT JOIN `tbl_event_gift_transaction` `trans` ON e.id=trans.event_id 
 WHERE cat.type ='campaign' 
 AND is_approved=1
 HAVING  e.funding_goal_amount <= amount
 GROUP BY `event_ac_id` LIMIT 8

1

votes

The filter on aggregate values is base on having

could be you need this

SELECT *, `e`.`id` AS `event_ac_id`, 
   SUM(CASE WHEN trans.gift_transaction_status = 1 THEN trans.event_gift_amount  
   ELSE 0 END) as amount 
FROM `tbl_event_category` `cat` LEFT JOIN `tbl_event` `e` 
       ON e.event_category=cat.id 
LEFT JOIN `tbl_organisation` `org` ON e.organisation_id=org.id 
LEFT JOIN `tbl_event_gift_transaction` `trans` ON e.id=trans.event_id 
 WHERE cat.type ='campaign' 
  AND is_approved=1
  HAVING  e.funding_goal_amount <= amount
  GROUP BY `event_ac_id` LIMIT 8

Unknown Column In Where Clause With Join

3 Answers