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

Question

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 user_id that have bought the commission fee product, but not the monthly plan and 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.

Thanks!

Similar question on Stack Overflow: [At least one X but no Ys Query](http://stackoverflow.com/q/9626965/2738262). Similar question on Database Administrators SE: [Self Join? Or am I wrong?](http://dba.stackexchange.com/q/80322/50436) — Damian Yerrick, Jun 02 '16 at 01:29

score 1 · Accepted Answer · edited May 23 '17 at 12:31

To find entities in a many-to-many table that have a row with value a for a particular attribute but no row with value 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 value b. (In a LEFT JOIN, if a row in the left table has no corresponding row in the right table, all columns from the right table in that row will be NULL.) Then the query keeps only rows where the right table's 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, self joins need to use a second copy of the data in another TEMPORARY TABLE to work around a known deficiency in MySQL's locks.)

score 0 · Answer 2 · answered Jun 02 '16 at 01:16

You can get such users using group by and having:

select user_id
from t
group by user_id
having sum(product = 'commission fee') > 0 and
       sum(product = 'monthly plan') = 0 and
       sum(product = 'yearly plan') = 0;

You can then get the original rows using logic like this:

select t.*
from t
where t.product = 'commission fee' and
      t.user_id in (select user_id
                    from t
                    group by user_id
                    having sum(product = 'commission fee') > 0 and
                           sum(product = 'monthly plan') = 0 and
                           sum(product = 'yearly plan') = 0
                   );

An alternative method uses not exists:

select t.*
from t
where t.product = 'commission fee' and
      not exists (select 1
                  from t t2
                  where t2.user_id = t.user_id and
                        t2.product in ('monthly plan', 'yearly plan')
                 );

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

2 Answers2