CODEWITHSUNDEEP
mysqlsql
elimariaaa
elimariaaa

Reputation: 866

Query error in WHERE clause

SELECT product_key.contact_email, product_key.client_name, product_key.status, product_key.key, payment.paymentdate, product_key.id, MAX(paymentdate) AS latest_payment, DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
FROM product_key LEFT OUTER JOIN payment ON payment.keyid=product_key.id 
WHERE product_key.status = 'purchased' AND expiration_date = DATE_ADD(NOW(), INTERVAL 10 DAY) GROUP BY product_key.id 
ORDER BY client_name asc

This is my query. I know I can't use an alias in a WHERE Clause since WHERE is first read before the SELECT. But even if I use something like this: 

SELECT product_key.client_name, DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
FROM product_key LEFT OUTER JOIN payment ON payment.keyid=product_key.id 
WHERE DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) = DATE_ADD(NOW(), INTERVAL 10 DAY) AND product_key.status = 'purchased' 
GROUP BY product_key.id 
ORDER BY client_name asc

Still an error. Please help. Thanks.

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You cannot use an alias defined in the select clause in a where clause. But that is ok, because you want to use having clause:

SELECT pk.contact_email, pk.client_name, pk.status, pk.key, p.paymentdate, pk.id,
       MAX(paymentdate) AS latest_payment,
       DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
FROM product_key pk LEFT OUTER JOIN
     payment p
     ON p.keyid = pk.id 
WHERE pk.status = 'purchased'
GROUP BY pk.id 
HAVING expiration_date = DATE_ADD(NOW(), INTERVAL 10 DAY)
ORDER BY client_name asc;

You can use the alias in the having clause. Also, I added table aliases to your query to make it more readable.

Upvotes: 1

peterm
peterm

Reputation: 92785

Since you're using an aggregate MAX() you should put it HAVING clause instead of WHERE. Assuming that other than that your query is correct and functional you can rewrite it like this

SELECT product_key.client_name, 
       DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
  FROM product_key LEFT OUTER JOIN payment 
    ON payment.keyid=product_key.id 
   AND product_key.status = 'purchased' 
 GROUP BY product_key.id 
HAVING DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) = DATE_ADD(NOW(), INTERVAL 10 DAY) 
 ORDER BY client_name

or

SELECT product_key.client_name, 
       DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
  FROM product_key LEFT OUTER JOIN payment 
    ON payment.keyid=product_key.id 
   AND product_key.status = 'purchased' 
 GROUP BY product_key.id 
HAVING expiration_date = DATE_ADD(NOW(), INTERVAL 10 DAY) 
 ORDER BY client_name

Upvotes: 1

Related Questions