Posted to tcl by kbk at Tue Dec 07 13:02:08 GMT 2010view raw
- 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