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

i have 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 need extract user_id have bought commission fee product, not monthly plan , yearly plan , show record commission fee product. basically, it'll display this:

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

thanks!

to find entities in many-to-many table have row value a particular attribute no row value b, can perform nonexistence self join. left join of table join criteria specify same entity , value b. (in left join, if row in left table has no corresponding row in right table, columns right table in row null.) query keeps rows right table's primary key is null, can happen such nonexistent row.

select cft.transac_id, cft.user_id, cft.product transactions cft left join transactions myp on (cft.user_id = myp.user_id     , myp.product in ('monthly plan', 'yearly plan')) cft.product = 'commission fee'   , myp.transac_id null 

(in mysql, if table temporary, self joins need use second copy of data in temporary table work around known deficiency in mysql's locks.)