• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

Who ordered certain products but no other

I would like to know who are the customers who ordered "a" product or "b" product but not other.

Example:
Customer 1 ordered: a, b, c, d products
Customer 2 ordered: a product
Customer 3 ordered: a, b products

If I want to get those customers who ordered  "a" product or "b" product but not other, then the select should give these rows:
Customer 2
Customer 3

Thank you
0
starhu
Asked:
starhu
1 Solution
 
Chris StanyonCommented:
Without seeing how your tables and columns are set up it's impossible to tell. Let us know your table structure and maybe show us some sample data for us to test against.
0
 
SharathData EngineerCommented:
Try this query.
SELECT CustomerNo
  FROM Customer
 GROUP BY CustomerNo
HAVING MIN(Product) IN ('a','b')
   AND MAX(Product) IN ('a','b')

Open in new window


I assume you have the table structure like this.

http://sqlfiddle.com/#!9/3fc41/1
0
 
snoyes_jwCommented:
There are several ways to do an exclusion. The one that usually performs best in MySQL is a LEFT JOIN:

SELECT DISTINCT customer_id 
FROM
  orders AS o1 
  LEFT JOIN orders AS o2 
    ON 
      o1.customer_id = o2.customer_id 
      AND o2.product NOT IN ('A', 'B')
WHERE 
  o1.product IN ('A', 'B')
  AND o2.customer_id IS NULL;

Open in new window

It's also possible with some subqueries. It's easier to understand what is going on, but won't perform as well in older versions of MySQL. Later versions have made some improvements.

SELECT customer_id
FROM customers
WHERE 
  customer_id IN (
    SELECT customer_id 
    FROM orders 
    WHERE product IN ('A', 'B')
  )
  AND customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE product NOT IN ('A', 'B')
  )

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Sharath_123's comment #a39653461
Assisted answer: 250 points for snoyes_jw's comment #a39721870

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
snoyes_jwCommented:
Suppose customer 4 has ordered products 'a', 'b', and 'apples'. Sharath's response will incorrectly return customer 4. That approach will only work in the special case that no products exist which are sorted between 'a' and 'b', and so should not be considered a viable solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now