Use the result of a Subquery in WHERE CLAUSE

Question

I'm try to use the result of a subquery in the query's WHERE clause. The attribute I am wishing to use is last_contact. See below.

  SELECT forename, surname, type,
         ( SELECT MAX(completed_date)
             FROM tblTasks
            WHERE prospect_id = tblProspects.prospect_id AND completed = '1'
         ) AS last_contact,
         created_at 
    FROM tblProspects 
   WHERE hidden != '1' AND type='Prospect' AND last_contact > DATE_ADD(CURDATE(), INTERVAL -90 DAY) 
ORDER BY last_contact ASC

I get the SQL Error: #1054 - Unknown column 'last_contact' in 'where clause'

Any help would be greatly appreciated.

Thanks.

M Khalid Junaid · Accepted Answer · 2014-02-18T05:55:33.200

2

You need to use HAVING clause in order to refine your results by custom aliased coulmn they cannot be used in where clause

SELECT 
forename, 
surname, 
type,
(SELECT MAX(completed_date) FROM tblTasks WHERE prospect_id = tblProspects.prospect_id AND completed = '1') AS last_contact, 
created_at 
FROM tblProspects 
WHERE hidden != '1' AND type='Prospect' 
HAVING (
last_contact > DATE_ADD(CURDATE(), INTERVAL -90 DAY)
OR last_contact IS NULL
)
ORDER BY last_contact ASC

edited Feb 18 '14 at 05:55

answered Feb 17 '14 at 21:02

M Khalid Junaid

63,861
10
90
118

1

Brilliant, thanks. I was trying with HAVING but had the syntax wrong! – Adam92 Feb 17 '14 at 21:05
Is there any way the HAVING clause can be made to return the null values also? At the moment it is only returning rows which have a value in last_contact. Not rows which have never been contacted. Thanks. – Adam92 Feb 17 '14 at 21:34

Use the result of a Subquery in WHERE CLAUSE

1 Answers1