Column alias not usable in WHERE clause

Question

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

SELECT
    contacts.*,
    orders.order_id AS is_customer,
    purchases.purchase_id AS is_supplier
FROM contacts
LEFT JOIN orders ON orders.customer_id=contacts.contact_id
LEFT JOIN purchases ON purchases.supplier_id=contacts.contact_id
WHERE is_customer IS NOT NULL
AND contacts.account_id=1
GROUP BY contact_id
ORDER BY contact_company_name

It's the line WHERE is_customer IS NOT NULL that's the problem - MySQL tells me it doesn't exist. I understand why this is so but that doesn't help me.

I have tried changing it to HAVING is_customer IS NOT NULL based on a tip I read here Unknown Column In Where Clause, thus:

SELECT
    contacts.*,
    orders.order_id AS is_customer,
    purchases.purchase_id AS is_supplier
FROM contacts
LEFT JOIN orders ON orders.customer_id=contacts.contact_id
LEFT JOIN purchases ON purchases.supplier_id=contacts.contact_id
HAVING is_customer IS NOT NULL
AND contacts.account_id=1
ORDER BY contact_company_name

...and this query works just fine providing I remove the GROUP BY clause. Obviously this breaks my result set.

Can anyone help me polish off this query please?

I don't see any aggregations so placing WHERE orders.order_id is not null would work — FirebladeDan, Jul 10 '15 at 18:08

score 0 · Answer 1 · answered Jul 10 '15 at 21:53

Not sure if it's generally done to answer your own question but I've got it working by having a read of this page https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html.

Turns out the statement worked with the GROUP BY just fine, I simply needed to rearrange the order of the statement. The GROUP BY comes after the WHERE but before the HAVING, like so:

SELECT
    contacts.*,
    orders.order_id AS is_customer,
    purchases.purchase_id AS is_supplier
FROM contacts
LEFT JOIN orders ON orders.customer_id=contacts.contact_id
LEFT JOIN purchases ON purchases.supplier_id=contacts.contact_id
AND contacts.account_id=1
GROUP BY contact_id
HAVING is_supplier IS NOT NULL
ORDER BY contact_company_name

Column alias not usable in WHERE clause

1 Answers1