Search code examples
javamysqlsqldatabasesqlyog

Unknown column 'readingid' in 'where clause'

SELECT
a.objid AS acctid,
a.acctno,
a.name,
a.meterid,
m.serialno,
(
    SELECT
    objid
    FROM
    waterworks_meter_reading r
    WHERE r.meterid = a.meterid
    AND r.month = 6
    AND r.year = 2015
    LIMIT 1
) AS readingid
FROM waterworks_account a
INNER JOIN waterworks_meter m ON a.meterid = m.objid
INNER JOIN waterworks_account_address ad ON a.objid = ad.parentid
WHERE ad.barangayid LIKE '%'
AND readingid IS NULL

When I tried to execute the query above it throws an error: Unknown column 'readingid' in 'where clause'. Can someone explain to me why?

Solution

  • In SQL, you cannot reference a column alias defined in a select in the where clause at the same level (or elsewhere in the select).

    MySQL has a convenient work-around, in some cases. You can reference it in a having clause. So this should do what you want:

    WHERE ad.barangayid LIKE '%'
HAVING readingid IS NULL