Mysql语言select嵌套语句_坚持使用mysql嵌套的select语句

我有三个表，我加入了一个select语句：

表CRM_CONTACTS结构：

CONTACTS_ID, CONTACTS_EMAIL

1 email@email.com

2 email2@email.com

表CRM_PRODUCTS结构：

PRODUCTS_ID, PRODUCTS_NAME, PRODUCTS_TYPE

204 Sample free_sample

205 beginners_1 monthly_subscription

206 beginners_2 monthly_subscription

表CRM_PRODUCTS_PURCHASE：

ID, CONTACTS_ID, PRODUCTS_ID

3 1 204

4 1 205

5 2 204

因此，客户当然可以购买多种产品 .

我想制作一个select语句，选择购买PRODUCT_ID 204(免费样品)的所有客户，但如果他们购买了PRODUCT_TYPE = monthly_subscription的产品，我不想在结果中使用它们 .

所以我想要的预期输出是与CONTACTS_ID 2的联系 .

我是sql语句的新手 . 这是我到目前为止所得到的：

SELECT CRM_PRODUCTS_PURCHASE.CONTACTS_ID,CRM_PRODUCTS_PURCHASE.PRODUCTS_ID, CRM_CONTACTS.CONTACTS_EMAIL, CRM_CONTACTS.CONTACTS_LANGUAGE

FROM CRM_PRODUCTS_PURCHASE

LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID

LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID

WHERE CRM_PRODUCTS_PURCHASE.CONTACTS_ID IN

(SELECT CRM_CONTACTS.ID

FROM CRM_PRODUCTS_PURCHASE

LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID

LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID

WHERE CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = 204 AND

CRM_CONTACTS.CONTACTS_EMAIL!='' AND

NOT coalesce(CRM_CONTACTS.CONTACTS_DEACTIVATED,0)

GROUP BY CRM_CONTACTS.CONTACTS_EMAIL

ORDER BY CRM_CONTACTS.CONTACTS_LANGUAGE)