?
Solved

Who ordered certain products but no other

Posted on 2013-11-13
7
Medium Priority
?
53 Views
Last Modified: 2016-05-22
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
Comment
Question by:starhu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39644399
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39653461
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
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 2000 total points
ID: 39721870
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41609650
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 41609651
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

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question