mysql - Column alias not usable in WHERE clause -

i've written php script builds query based on keyword/select menu search system. script works great right until try searching on column alias:

select     contacts.*,     orders.order_id is_customer,     purchases.purchase_id is_supplier contacts left join orders on orders.customer_id=contacts.contact_id left join purchases on purchases.supplier_id=contacts.contact_id is_customer not null , contacts.account_id=1 group contact_id order contact_company_name 

it's line where is_customer not null that's problem - mysql tells me doesn't exist. understand why doesn't me.

i have tried changing having is_customer not null based on tip read here unknown column in clause, thus:

select     contacts.*,     orders.order_id is_customer,     purchases.purchase_id is_supplier contacts left join orders on orders.customer_id=contacts.contact_id left join purchases on purchases.supplier_id=contacts.contact_id having is_customer not null , contacts.account_id=1 order contact_company_name 

...and query works fine providing remove group by clause. breaks result set.

can me polish off query please?

not sure if it's done answer own question i've got working having read of page https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html.

turns out statement worked group by fine, needed rearrange order of statement. group by comes after where before having, so:

select     contacts.*,     orders.order_id is_customer,     purchases.purchase_id is_supplier contacts left join orders on orders.customer_id=contacts.contact_id left join purchases on purchases.supplier_id=contacts.contact_id , contacts.account_id=1 group contact_id having is_supplier not null order contact_company_name