Solved

Who ordered certain products but no other

Posted on 2013-11-13
7
42 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
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 40

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 500 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 142

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now