Stuck on mysql nested select statement

Question

I have three tables which I join in a select statement:

Table CRM_CONTACTS structure:

CONTACTS_ID, CONTACTS_EMAIL
1            email@email.com
2            email2@email.com

Table CRM_PRODUCTS structure:

PRODUCTS_ID, PRODUCTS_NAME, PRODUCTS_TYPE
204          Sample         free_sample
205          beginners_1    monthly_subscription
206          beginners_2    monthly_subscription

Table CRM_PRODUCTS_PURCHASE:

ID, CONTACTS_ID, PRODUCTS_ID
3   1            204
4   1            205
5   2            204

So customers can of course buy multiple products.

I would like to make a select statement that selects all customers that bought PRODUCT_ID 204 (free sample), but I do not want to have them in my result if they bought a product of PRODUCT_TYPE=monthly_subscription

So my expected output I want is the contact with CONTACTS_ID 2.

I am pretty new to sql statements. This is what I got so far:

    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)

Lets get some sample data in your tables for us to look at as well as an expected output. — Matt, Aug 29 '18 at 09:35
NOT coalesce I use because the contacts_deactivated can be either 0 or Null in my DB — Sven, Aug 29 '18 at 09:44

score 0 · Answer 1 · answered Aug 29 '18 at 09:38

First of all you mentioned ORDER BY CRM_CONTACTS.CONTACTS_LANGUAGE, which is not in SELECT statement. How you can use GROUP BY in sub Query where you return single column.

(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)

I did not mention the CONTACTS_LANGUAGE as it's of no real importance for the output. The group_by is necesary as a mulitple contacts can have the same email address and I only want to send out my emails to them once. — Sven, Aug 29 '18 at 09:43

Martin · Answer 2 · 2018-08-29T10:37:38.067

You should avoid nested sub-selects where possible as they are hard for the MySQL internal optimiser to work with and hence tend to generate very slow queries. In many cases it is often better to create an interim table with the sub-select data in it and join it, than do a sub-select but this is quite situational.

However sometimes you need to and in those cases try and keep the sub-select as simple as possible.

SELECT *
FROM CRM_PRODUCTS_PURCHASE
   # Do other joins 
WHERE
    CRM_PRODUCTS_PURCHASE.CONTACTS_ID NOT IN (
        SELECT CONTACTS_ID
        FROM CRM_PRODUCTS_PURCHASE
        WHERE
            CRM_PRODUCTS_PURCHASE.PRODUCTS_ID IN (205, 206))
    AND CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = 204
;

- The above is untested

NOTE: ORDER BY in a sub-select will usually have no effect. Leave ORDER BY till last i.e. mysql - order by inside subquery

In our real world cases we often found that it was orders of magnitude better to run the above as 2 queries. i.e. generate a list of CONTACTS_ID and store the value in an PHP variable, then make a 2nd query that used those values in the NOT IN clause.

Stuck on mysql nested select statement

2 Answers2