Issue with a conditional sub query
Not sure that I got it right, but:

select tag, main_tag
from foo 
group by tag, main_tag
having sum(value) = 0;


retrieves tag, main_tag where sum(value) is 0. You can join this result with the original table as:

select f.* 
from foo f
join ( 
    select tag, main_tag
    from foo 
    group by tag, main_tag
    having sum(value) = 0
) as t
    on t.tag = f.tag
   and t.main_tag = f.main_tag;