Solved

Who ordered certain products but no other

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

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

10 Experts available now in Live!

Get 1:1 Help Now