CODEWITHSUNDEEP
mysqlsql
Levi Putna
Levi Putna

Reputation: 3073

MYSQL select unique id's where column value is not null in one or more records

Background

I have a table that contains customer purchase records, the table has a column named coupon_id, this column is null if a customer didn't use a coupon during a purchase and contained the coupon id if they did. The table can contain several purchases for a single customer, a customer may have used a coupon during one purchase and not during another.

+-------+-------------+-----------+-------------+
| ID    | customer_id | coupon_id | other_data  | 
+-------+-------------+-----------+-------------+
| 0     |  1          | 32        | ...         |
| 1     |  1          | null      | ...         |
| 2     |  1          | null      | ...         |
| 3     |  1          | null      | ...         |
| 4     |  2          | null      | ...         |
| 5     |  3          | 11        | ...         |
| 6     |  4          | null      | ...         |
| 7     |  4          | null      | ...         |
+-------+-------------+-----------+-------------+

Problem

I want to get the customer_id for all customers who have never made a purchase using a coupon. The current query I am using is still returning customers that have made a purchase with a coupon.

SELECT customer_id from checkouts c 
WHERE code_id IS NULL
GROUP BY customer_id 
ORDER BY customer_id asc

Question

How can I select a list of unique id's for costumers who have never purchased using a coupon?

Upvotes: 1

Views: 2518

Answers (1)

willy
willy

Reputation: 1490

Use WHERE NOT EXISTS and a subquery. Something like:

    SELECT DISTINCT
        customer_id
    FROM checkouts c
    WHERE NOT EXISTS (
        SELECT *
        FROM checkouts
        WHERE
            checkouts.customer_id = c.customer_id
            AND coupon_id IS NOT NULL
    ) cc;

Not entirely sure if that's the correct MySQL syntax, but that's the gist of it.

Upvotes: 3

Related Questions