问题描述:

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.

网友答案:

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.

相关阅读:
Top