(Not my real MySQL schema, but illustrates what needs done.)
Users can belong to many groups, and groups have many users.
users:
id INT
validated TINYINT(1)
groups:
id INT
name VARCHAR(20)
groups_users:
group_id INT
user_id INT
I need to find groups that contain both validated and unvalidated users (validated being 1 or 0, respectively), in order to perform a specific manual maintenance task. There are thousands of users, all belong to at least one group, but a group usually only has 2-5 users.
This is a live production server, so I could probably craft a query myself, but the last one I tried took a matter of minutes before I killed it. (I'm not one of those brilliant SQL wizards.) I suppose I could take the server down for maintenance, but, if possible, a query that gets this job done in a matter of seconds would be fantastic.
Thanks!