Kubilay Kaan Durmuş Kubilay Kaan Durmuş - 6 months ago 15
SQL Question

Alias name causing error in the where clause - MySQL

My SQL code :

SELECT (
SELECT COUNT(1)
FROM FAVORI_DUALAR
WHERE DuaID = D.DuaID
AND PID = 'L3FNCPEVME36'
) AS Eklimi,
(
SELECT COUNT(1)
FROM EDILEN_DUALAR
WHERE DuaID = D.DuaID
AND PID = 'L3FNCPEVME36'
) AS Okudum,
D.Tarih,
D.DuaID,
D.DuaBaslik,
D.DuaTuru,
D.DuaSayisiSiniri,
D.DuaIcerik,
D.DuaMeal,
D.DuaArapca,
D.PID,
(
SELECT COUNT(1)
FROM EDILEN_DUALAR
WHERE DuaID = D.DuaID
) AS Okundu
FROM DUALAR D
WHERE D.Aktif = '1'
AND (D.DuaTuru = 2 OR D.DuaTuru = 1 AND Okudum = 0)
ORDER BY
D.Tarih DESC
LIMIT 9



Error : #1054 - Unknown column 'Okudum' in 'where clause'


I wrote

SELECT COUNT(1)
FROM EDILEN_DUALAR
WHERE DuaID = D.DuaID
AND PID = 'L3FNCPEVME36') AS Okudum


But still I get the error in the query.

Answer

Your Okudum field is a calculated field in the query. This can not be directly referenced (only in ORDER BY).

So you can use in the where clause:

WHERE ... AND (SELECT COUNT(1) FROM   EDILEN_DUALAR WHERE DuaID = D.DuaID AND PID = 'L3FNCPEVME36') = 0

or you can make a subquery: Make a join with EDILEN_DUALAR as a subquery.

SELECT (
           SELECT COUNT(1)
           FROM   FAVORI_DUALAR
           WHERE  DuaID       = D.DuaID
                  AND PID     = 'L3FNCPEVME36'
       )       AS Eklimi,
       ED.Okudum,
       D.Tarih,
       D.DuaID,
       D.DuaBaslik,
       D.DuaTuru,
       D.DuaSayisiSiniri,
       D.DuaIcerik,
       D.DuaMeal,
       D.DuaArapca,
       D.PID,
       (
           SELECT COUNT(1)
           FROM   EDILEN_DUALAR
           WHERE  DuaID = D.DuaID
       )       AS Okundu
, D.Aktif
FROM   DUALAR D LEFT JOIN
    (SELECT DuaID, COUNT(1) AS Okudum
           FROM   EDILEN_DUALAR
           WHERE  PID     = 'L3FNCPEVME36'
                  GROUP BY DuaID       ) ED

ON ED.DuaID = D.DuaID
WHERE   D.Aktif = '1'
       AND (D.DuaTuru = 2 OR D.DuaTuru = 1 AND (ED.Okudum IS NULL OR ED.Okudum = 0))
ORDER BY
       D.Tarih DESC