New records with common data

I have a table like this (MySQL 5.0.x, MyISAM):

user{id, login, ip, banned} (Banned: 0 false, 1 true)

I would like to find all users not banned (banned=0) if at least 5 other users with the same ip have already been banned (banned=1).

Thanks for your help! :)

From stackoverflow

• If you have lots of banned users:

  SELECT  *
  FROM    user uo
  WHERE   EXISTS (
          SELECT  1
          FROM    user ui
          WHERE   ui.ip = uo.ip
                  AND banned = 1
          LIMIT 4, 1
          )
          AND banned = 0
  

  If you have few banned users:

  SELECT  *
  FROM    user
  WHERE   ip IN (
          SELECT  ip
          FROM    user
          WHERE   banned = 1
          GROUP BY
                  ip
          HAVING  COUNT(*) >= 5
          )
          AND banned = 0
  

  In both cases, having an index on (ip, banned) will improve these queries a lot.

  Toto : Exactly what I was looking for: I wanted to avoid the "SELECT ... IN ()" for performance issue. Thanks a lot. :)

• SELECT id
  FROM user
  WHERE ip IN (
   SELECT DISTINCT ip
   FROM user
   WHERE COUNT(*) >= 5
    AND banned = 1
   GROUP BY ip
  )
   AND banned = 0
  

  Toto : Thanks a lot! :)