MySQL:其中联接表中的最大日期小于

我有三张桌子- client , invoice 和 invoice_item . 我需要选择所有客户,他们的最后购买日期小于特定日期。购买存储日期 发票项 表,分配给发票的项目,以及分配给客户的发票。这是我试过的

SELECT c.* FROM client c
INNER JOIN (
            SELECT i.client_id as clid, MAX(item.date_created) as mdt
            FROM invoice i
            INNER JOIN invoice_item item on i.id = item.invoice_id
            GROUP BY i.id
            ) joined
ON joined.clid = c.id
WHERE joined.mdt  < date('2017-01-01')

以及

SELECT c.* FROM client c
WHERE c.id IN (
            SELECT DISTINCT i.client_id FROM invoice i
            INNER JOIN invoice_item item on i.id = item.invoice_id
            GROUP BY i.id
            HAVING MAX(item.date_created) < date('2017-01-01')
            )

两个查询都返回错误的结果,下面是一个用于检查正确性的查询:

SELECT MAX(invoice_item.date_created) FROM invoice_item
INNER JOIN invoice i on invoice_item.invoice_id = i.id 
WHERE client_id = {some id from the previous query};

上面的查询返回日期,大于 2017-01-01 对于一些客户。

我做错什么了?谢谢。