Unknown column 'expiry_date' in 'where clause' my sql query

Question

I have multiple tables and I am using the following query to filter records by expiry_date which are not expired yet.

My query:

SELECT

    MT.id,
    CONCAT(MEM.name,' ',MEM.last_name) AS name,
    IFNULL((SELECT MAX(MFT.membership_end_date) 
        FROM membership_future_transaction AS MFT 
        WHERE MFT.membership_transaction_id = MT.id),MM.end_date) AS expiry_date,
    MEM.phone,
    MEM.email,
    MMST.name AS membership_name,
    MMST.price,
    MMST.session
FROM
    membership_transaction AS MT
    LEFT JOIN member_master AS MEM ON MEM.id = MT.member_id
    LEFT JOIN members_membership AS MM ON MM.membership_transaction_id = MT.id
    LEFT JOIN membership_master AS MMST ON MMST.id = MT.membership_id
WHERE
    MT.is_casual = 'No'
    AND MT.is_deleted = 'No'
    AND MM.is_cancelled = 'No'
    AND expiry_date >= '2016-01-01'"

But I get Unknown column 'expiry_date' in 'where clause' error please help me what am I missing here.

duplicate of [using-column-alias-in-where-clause-of-mysql-query-produces-an-error](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — amdixon, Jul 11 '15 at 07:00
You can't use WHERE to reference an alias in this way. You can use HAVING or you can use WHERE and write out the whole aliased thing longhand (which will be faster) — Strawberry, Jul 11 '15 at 08:06

score 0 · Answer 1 · answered Jul 11 '15 at 10:46

MySQL has a perhaps nifty feature where you can use having with an alias. So if you change the where clause to:

WHERE MT.is_casual = 'No' AND
      MT.is_deleted = 'No' AND
      MM.is_cancelled = 'No'
HAVING expiry_date >= '2016-01-01'"

Then it should work. Of course, this won't work as you expect for a query with a group by.

Unknown column 'expiry_date' in 'where clause' my sql query

1 Answers1