CODEWITHSUNDEEP
mysqlsqldatabase
James Wilson
James Wilson

Reputation: 809

Struggling with MySQL NOT EXISTS

I am trying to query our database to pull out customer data. The crucial parts are:

Pull out all customers/orders that haven't bought product_a.

This will list out customers/ orders who have purchased, product_b, product_c and product_d.

But it needs to make sure that the customer hasn't EVER purchased product_a. I need them excluded.

Is this the right way to go about the NOT EXISTS? I still feel like it's including some records that bought product_a.

SELECT
    *
FROM
    orders
JOIN customers AS cus ON orders.CustomerNumber = cus.CustomerNumber
WHERE
    product != 'product_a'
OR (
    HomeTelephone = ''
    AND MobileTelephone != ''
)
AND NOT EXISTS  (
    SELECT
        OrderNumber
    FROM
        orders AS o
    JOIN customers AS c ON o.CustomerNumber = c.CustomerNumber
    WHERE
        c.EmailAddress = cus.EmailAddress
    AND Product = 'product_a'
    AND completed = 1
)
ORDER BY
    orderdate

Without the NOT EXISTS statement, a customer record could be included even if they have bought product_a separately right?

Upvotes: 0

Views: 57

Answers (2)

Strawberry
Strawberry

Reputation: 33945

SELECT c.* 
  FROM customers c
  LEFT
  JOIN orders o
    ON o.CustomerNumber = c.CustomerNumber 
   AND o.product = 'product_a'
 WHERE o.CustomerNumber IS NULL

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

Your Not Exists is a little off and where product != 'product_a' is redundant. 

SELECT
    *
FROM

    orders AS o1
    JOIN customers AS cus ON o1.CustomerNumber = cus.CustomerNumber
WHERE
    cus.HomeTelephone = ''
    AND cus.MobileTelephone != ''
    AND NOT EXISTS  (
        SELECT 
            1 
        FROM 
            orders o2
        WHERE
            o2.CustomerNumber = cus.CustomerNumber 
            AND Product = 'product_a'
            AND completed = 1
    )
ORDER BY
    o1.orderdate

This will give you the customers with their orders. Based on your description though, if you were wanting just the customer information, you could exclude the join to orders in the first part of your query, and use the Not Exist to determine if that customer purchased product_a or not.

SELECT
    *
FROM
    customers cus
WHERE
    HomeTelephone = ''
    AND MobileTelephone != ''
    AND NOT EXISTS  (
        SELECT 
            1 
        FROM 
            orders o
        WHERE
            o.CustomerNumber = cus.CustomerNumber 
            AND Product = 'product_a'
            AND completed = 1
    )

Upvotes: 1

Related Questions