实现了透视列的值的子查询中的WHERE子句？(Implementing a WHERE clause

我已经成功地设法执行一些表连接，并使用数据透视表（与奥利·琼斯的帮助下）产生的结果。 SQL语句和结果如下。

我想结果返回给唯一有透视列“日期”的值的行（我认为这是术语！？）今天或将来成立。从我所看到的WHERE date >= CURDATE()因为“日期”不存在技术上我收到执行一个错误，当在声明的末尾添加到WHERE子句然而应该做的工作。我不知道如何将它融入我的第二子查询，任何帮助将非常感激:)

提前致谢

SELECT 
    content.id as id, content.alias as alias,

    ( 
        SELECT modx_site_tmplvar_contentvalues.value FROM modx_site_tmplvar_contentvalues 
        WHERE modx_site_tmplvar_contentvalues.tmplvarid = 324 
        AND modx_site_tmplvar_contentvalues.contentid = content.id 
     ) AS featured, 

    ( 
        SELECT modx_site_tmplvar_contentvalues.value FROM modx_site_tmplvar_contentvalues 
        WHERE modx_site_tmplvar_contentvalues.tmplvarid = 289 
        AND modx_site_tmplvar_contentvalues.contentid = content.id

     ) AS date

    FROM modx_site_content AS content 

    LEFT JOIN 
        modx_site_tmplvar_contentvalues AS tv_values
        ON tv_values.contentid = content.id 

    WHERE content.parent = 1842 
     AND content.published = 1 

    GROUP BY tv_values.contentid 
    ORDER BY featured DESC, date ASC

一种选择SI改变“日期”一节，以“左连接”做到这一点：

SELECT 
    content.id as id, content.alias as alias,

    ( 
        SELECT modx_site_tmplvar_contentvalues.value FROM modx_site_tmplvar_contentvalues 
        WHERE modx_site_tmplvar_contentvalues.tmplvarid = 324 
        AND modx_site_tmplvar_contentvalues.contentid = content.id 
     ) AS featured, 

    date_values.value as date

    FROM modx_site_content AS content 

    LEFT JOIN 
        modx_site_tmplvar_contentvalues AS tv_values
        ON tv_values.contentid = content.id

    LEFT JOIN
        modx_site_tmplvar_contentvalues AS date_values
        ON date_values.contentid = content.id

WHERE content.parent = 1842 
    AND content.published = 1 
    AND date_values.tmplvarid = 289
    AND date_values.value >= CURDATE() 

GROUP BY tv_values.contentid 
ORDER BY featured DESC, date ASC