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

  1. select distinct email, registered
  2. from thetable
  3. where registered = 1
  4. and onlist = 1 -- people who are registered.
  5. -- you didn't specify what happens if
  6. -- someone is registered twice, once
  7. -- 'onlist' and once not.
  8. union
  9. select distinct o.email as email, 0 as registered
  10. from thetable o
  11. left outer join thetable i on i.email = o.email and i.registered = 1
  12. where i.email is null
  13. and o.registered = 0
  14. and o.onlist = 1
  15. group by email, registered -- people who are not registered,
  16. -- you didn't specify what to do if
  17. -- an unregistered person is both
  18. -- onlist and not onlist