How to select customers that only bought A and B without a subquery

Question

How do I create a SQL query that shows people who bought product A and who bought product B without using a subquery. Right now I am trying Where Product IN ('A' , 'B' ), without count distinct because I also need that information. Any ideas? Right now I am getting any A's and any B's. I want it to look like

JIM 03/20/2014 Product A
JIM 03/20/2014 Product B

And exclude anyone who doesn't purchase both. It currently looks like

SELECT First, Last, Date, Product
From TABLE1
Where (Date = @Today) AND (Product IN ('A' and 'B'))

(Also A and B are the only options in the product table)

You are missing a closing bracket at the end of your `WHERE` clause. — Paul, Mar 20 '14 at 13:50
do you mean `(Product IN ('A', 'B'))`? the logical `AND` of two non-null strings will be `TRUE`. — ely, Mar 20 '14 at 13:50
Also, (not that it matters hugely for this) what flavour of SQL are you using? — Paul, Mar 20 '14 at 13:52
Take a look here: http://stackoverflow.com/questions/17803830/mysql-select-from-multiple-rows-same-user-different-values — Spork, Mar 20 '14 at 13:55

Bohemian · Answer 1 · 2014-03-21T14:21:41.440

2

Use group and having with a condition that requires both products to have been purchased:

SELECT First, Last, Date
From TABLE1
Where Date = @Today
AND Product IN ('A', 'B')
GROUP BY First, Last, Date
HAVING COUNT(DISTINCT Product) = 2

The key is adding DISTINCT to the COUNT(), which changes the result from the simple number of transactions to the number of different products purchased.

edited Mar 21 '14 at 14:21

answered Mar 20 '14 at 13:51

Bohemian

412,405
93
575
722

1

As written, this will never return a row because `COUNT(DISTINCT Product)` in this query will never evaluate to 2. – Andriy M Mar 21 '14 at 12:24
1

@AndriyM is correct, the `Product` should be removed from the `GROUP BY` list. – ypercubeᵀᴹ Mar 21 '14 at 12:46
@AndriyM quite right. Copy paste is a two-edged sword. thx – Bohemian Mar 21 '14 at 14:22
@ypercube as are you (correct) – Bohemian Mar 21 '14 at 14:23
COUNT(DISTINCT Product) does result in 0 rows. The Product is not in the group by. How can this be fixed? – ColorfulWind Apr 17 '14 at 18:42
What do you mean exactly? – Bohemian Apr 18 '14 at 02:47

How to select customers that only bought A and B without a subquery

1 Answers1