Unable to use a field in a WHERE clause in a SQL query
I have this query:
select
l.lead_id, l.lead_date_received,
TIMESTAMPDIFF(MINUTE, l.lead_date_received, NOW()) AS minutes,
s.admin_id, a.name, a.email
from
leads l
inner join sa开发者_运维技巧les_emails s on l.lead_id = s.lead_id
inner join admin a on a.admin_id = s.admin_id
where
not exists (select 1 from comments c where l.lead_id = c.lead_id)
order by
minutes, l.lead_date_received desc
Now I want to put up condition in WHERE
clause like minutes > 30
. However, as you can see, minutes
only appears in the SELECT
clause not in FROM
clause or sub query, meaning my condition like minutes > 30
won't be recognized in the WHERE
clause.
How do I fix this?
SELECT l.lead_id, l.lead_date_received, TIMESTAMPDIFF(MINUTE, l.lead_date_received, NOW()) AS minutes, s.admin_id,
a.name, a.email
FROM leads l
INNER JOIN sales_emails s
ON l.lead_id = s.lead_id
INNER JOIN admin a
ON a.admin_id = s.admin_id
WHERE NOT EXISTS (SELECT 1 FROM comments c WHERE l.lead_id = c.lead_id)
AND TIMESTAMPDIFF(MINUTE, l.lead_date_received, NOW()) > 30
ORDER BY minutes, l.lead_date_received DESC
OR add another query:
SELECT *
from
(
SELECT l.lead_id, l.lead_date_received, TIMESTAMPDIFF(MINUTE, l.lead_date_received, NOW()) AS minutes, s.admin_id,
a.name, a.email
FROM leads l
INNER JOIN sales_emails s
ON l.lead_id = s.lead_id
INNER JOIN admin a
ON a.admin_id = s.admin_id
WHERE NOT EXISTS (SELECT 1 FROM comments c WHERE l.lead_id = c.lead_id)
ORDER BY minutes, l.lead_date_received DESC
)
Where minutes > 30
WITH Something as (
SELECT l.lead_id,
l.lead_date_received,
TIMESTAMPDIFF(MINUTE, l.lead_date_received, NOW()) AS minutes,
s.admin_id,
a.name, a.email
FROM leads l
INNER JOIN sales_emails s ON l.lead_id = s.lead_id
INNER JOIN admin a ON a.admin_id = s.admin_id
WHERE NOT EXISTS (SELECT top 1 1 FROM comments c WHERE l.lead_id = c.lead_id)
)
Select * FROM Something
WHERE minutes > 30
ORDER BY minutes, lead_date_received DESC
精彩评论