CODEWITHSUNDEEP
sqlsql-servert-sql
DPool34
DPool34

Reputation: 85

Best way of excluding customers with a specific order

I'm running a pretty basic query. The issue is I'm trying to get results with all of our customers who never ordered product Y. The problem is, if I use a simple WHERE ProductColumn <> 'Product Y', it doesn't work because almost all of our customers have ordered other products.

Basically, I'm wondering how I could exclude on the customer level (instead of the order level) - if a customer has ordered Product Y, I don't want them showing up in my results at all.

Thanks.

Upvotes: 0

Views: 369

Answers (3)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

Simple LEFT JOIN should work:

SELECT c.*
FROM customers c
    LEFT OUTER JOIN orders o
        ON o.customerid = c.customerID
WHERE o.ProductColumn <> 'Product Y'

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37420

Try this:

select * from customers c
where not exists(select 1 from customers
                 where cutomer_id = c.customer_id
                 and productcolumn = 'product y')

This assumes you have 'customer_id' column (or at least some id column in your table).

Upvotes: 2

dfundako
dfundako

Reputation: 8324

You are probably looking for EXISTS().

If I want to find customers who have placed orders:

SELECT c.*
FROM customers c
WHERE EXISTS (
SELECT 1 
FROM orders o
WHERE o.customerid = c.customerID
AND productID = 'Y'
)

If I want to find customers who have not placed orders:

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1 
FROM orders o
WHERE o.customerid = c.customerID
AND productID = 'Y'
)

Upvotes: 3

Related Questions