CODEWITHSUNDEEP
mysqlsql
Sequel3
Sequel3

Reputation: 75

Retrieve 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

Upvotes: 0

Views: 74

Answers (2)

Matt
Matt

Reputation: 14381

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(*)

Upvotes: 2

Barmar
Barmar

Reputation: 782624

Add this to your WHERE clause:L

AND [Transaction_No] NOT IN (
    SELECT [Transaction_No]
    FROM [MystoreDB$Transaction lines]
    where [Product ID] <> 'A'
)

to exclude customers who bought some other product.

Upvotes: 0

Related Questions