select *
from (select b.*
from (select a.*, (
if(`sid` = 8 AND `rid` = 1 AND `nid` IS NULL, 1<<0, 0)
| if(`sid` IS NULL AND `rid` IS NULL AND `nid` = "привет", 1<<1, 0)
| if(`sid` = 1 AND rid = 2 AND `nid` IS NULL, 1<<2, 0) ) flag
from `table` a
where 1=1) b
group by b.main
having bit_or(b.flag) = 7 )c
join (select a.*, (
if(`sid` = 8 AND `rid` = 1 AND `nid` IS NULL, 1<<0, 0)
| if(`sid` IS NULL AND `rid` IS NULL AND `nid` = "привет", 1<<1, 0)
| if(`sid` = 1 AND rid = 2 AND `nid` IS NULL, 1<<2, 0) ) flag
from `table` a
where 1=1) d using(main)
