[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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