Posted to tcl by kbk at Tue Dec 07 13:02:08 GMT 2010view pretty

select distinct email, registered
from thetable
where registered = 1
and onlist = 1             -- people who are registered.
                           -- you didn't specify what happens if
                           -- someone is registered twice, once
                           -- 'onlist' and once not.
union
select distinct o.email as email, 0 as registered
from thetable o 
left outer join thetable i on i.email = o.email and i.registered = 1
where i.email is null
and o.registered = 0
and o.onlist = 1
group by email, registered -- people who are not registered,
                           -- you didn't specify what to do if
                           -- an unregistered person is both
                           -- onlist and not onlist