CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
Programmer
Programmer

Reputation: 1294

Select all customers who do not hold any products

customer_ID product_id product_returned_date
1           a          12/12/2018
1           b          NULL
2           a          NULL
3           b          24/02/2018
2           c          NULL
1           c          23/05/2017

In the above scenario, there are three customers with ID 1,2,3. There are three products with ID a,b,c. The return date specifies the date on which a product was returned. If it is NULL, the product was not returned. I would like to query all the customer_IDs who do not hold ANY products at all.

Customer 1 currently holds products b as they have returned a and c.

Customer 2 currently holds product a and c.

Customer 3 has returned product b and hence doesn't currently hold any products.

Hence, I want to return customer 3.

Attempt: select customer_ID from table where product_returned_date is not null

However, this doesn't ensure that the customer isn't holding any products.

Upvotes: 1

Views: 131

Answers (3)

zlori
zlori

Reputation: 16

This should select all customer_ID's from the table where a row with a null return date does not exist (all products returned).

SELECT
    a.customer_ID
FROM
    table a
WHERE
    NOT EXISTS (
        SELECT
            customer_ID
        FROM
            table
        WHERE
            customer_ID = a.Customer_ID
            AND
            product_returned_date IS NULL
    )

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13016

with this scenario 

3           b          24/02/2018
3           b          24/02/2018

i'm thinking using this query.

select customer_ID from tableA
group by customer_ID, product_id  
having count(distinct customer_ID, product_ID) = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270703

If I understand correctly, you want customers all of whose products have a return date. You can do this using aggregation:

select customer_id
from t
group by customer_id
having count(*) = count(product_returned_date);

Upvotes: 1

Related Questions