Jan Richmond Padilla Jan Richmond Padilla - 1 year ago 62
MySQL Question

Show only records that bought a specific product without buying another specific product

I have this hypothetical table

transac_id user_id product
---------- ------- -------
2051613 189546 monthly plan
8746169 189546 commission fee
7845946 998741 commission fee
8897155 166235 sms
6325477 166235 newsletter
8897452 166235 commission fee
4328941 302604 monthly plan
8897415 309888 sms
2564718 960007 commission fee
7451352 960007 yearly plan

What I need to extract is the
that have bought the
commission fee
product, but not the
monthly plan
yearly plan
and will only show the record with the
commission fee
product. So basically, it'll display this:

transac_id user_id product
---------- ------- -------
7845946 998741 commission fee
8897155 166235 commission fee.


Answer Source

To find a table where a particular entity has attributes of type a but not b, you can perform a nonexistence self join. This is a LEFT JOIN of a table to itself where the join criteria specify the same entity and the type of b. A LEFT JOIN that has no corresponding row in the right table will return all NULL values in the result set. So keep only those where the primary key is NULL, which can only happen with such a nonexistent row.

SELECT cft.transac_id, cft.user_id, cft.product
FROM transactions cft
LEFT JOIN transactions myp
ON (cft.user_id = myp.user_id
    AND myp.product in ('monthly plan', 'yearly plan'))
WHERE cft.product = 'commission fee'
  AND myp.transac_id is null

(In MySQL, if the table is TEMPORARY, you will need to make a copy of the table in a second TEMPORARY TABLE to work around a known deficiency in MySQL's locks.)