Sequel3 Sequel3 - 19 days ago 6
MySQL Question

Retreive rows which meets a certain condition

I would like to start by explaining what my query should do.

At my store,we sell products A,B,C and D(Product ID)
Let's say I am interested in only those transactions where Item A was sold

This is how i wrote my query

Select [Transaction_No],[Product ID]
from [MystoreDB$Transaction lines]
where Date = '01-Jan-2016'
and (Product ID) = 'A'


The query executes without any errors,and I get the results only filtered to Product ID A.

But if I really look into the filtered transactions, I can see that there were other products bought in the same transaction(Product B was bought as well)

But the query only filtered 'the rows' with Product A

For Instance
There were total of 4 transactions done on 1-Jan-2016

Transaction 1 had
Product A + B

Transaction 2 had
Product A only

Transaction 3 had
Product A + C

Transaction 4 had
Product A only

At the end I want my query to retrieve only 2 transactions
Which is Transaction 2 and 4(since only product A was purchased)
I will ignore Transactions 1 and 3 since another product was purchased along with product A

What I want to find out is all transactions that had only Product A.
This means,the customer only bought product A and no other products.
Not sure how to get this.

I am using MYSQL for the DB engine

All help is appreciated.

Answer
SELECT
    Transaction_No
FROM
    Transactions
WHERE
    Date = '01-Jan-2016'
GROUP BY
    Transaction_No
HAVING
    COUNT(CASE WHEN Product_Id = 'A' THEN Product_Id END) = COUNT(*)

Doing a group by with conditional aggregation will give you the desired result and as there are no sub selects etc it should preform faster than a NOT EXISTS solution.

Edit Per Your Comment:

To test to see if a customer bought both Product A & B but no other products you would have to add a couple of additional constraints in your HAVING clause. Test that COUNT of A > 0 and COUNT of B > 0 and then that the COUNT of A & B is the same as the COUNT of All Products.

SELECT
    Transaction_No
FROM
    Transactions
WHERE
    Date = '01-Jan-2016'
GROUP BY
    Transaction_No
HAVING
    COUNT(CASE WHEN Product_Id = 'A' THEN Product_Id END) > 0
    AND COUNT(CASE WHEN Product_Id = 'B' THEN Product_Id END) > 0
    AND COUNT(CASE WHEN Product_Id IN ('A','B') THEN Product_Id END) = COUNT(*)
Comments