Find groups with both validated, unvalidated users

Question

(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!

Answer 1

For each user, select all users with which he is in the same group. Then only select the users which validated differs.

SELECT users u1 LEFT JOIN groups_users g1 ON id=user_id LEFT JOIN groups_users g2 ON group_id=group_id LEFT JOIN users u2 ON user_id=id WHERE u1.validated=1 AND u2.validated=0;

Answer 2

Here's a complete example (I did it in Oracle, but the main SQL query should work fine on MySql).

CREATE TABLE USERS (ID INTEGER, validated NUMBER(1));
CREATE TABLE GROUPS (ID integer, NAME VARCHAR2(20));
CREATE TABLE groups_users (group_id integer, user_id INTEGER);

INSERT INTO USERS VALUES(1,0);
INSERT INTO USERS VALUES(1,1);
INSERT INTO USERS VALUES(2,0);
INSERT INTO USERS VALUES(3,0);
INSERT INTO USERS VALUES(3,1);

INSERT INTO GROUPS VALUES(1,'g1');
INSERT INTO GROUPS VALUES(2,'g2');
INSERT INTO GROUPS VALUES(3,'g3');

INSERT INTO groups_users VALUES (1,1);
INSERT INTO groups_users VALUES (2,2);
INSERT INTO groups_users VALUES (3,3);

 SELECT
DISTINCT g.id
    FROM groups g
   WHERE
          EXISTS (SELECT *
                   FROM GROUPS_USERS gu
                      , USERS u
                  WHERE gu.user_id = u.id
                    AND gu.group_id = g.id
                    AND u.validated = 0)
    AND   EXISTS (SELECT *
                   FROM GROUPS_USERS gu
                      , USERS u
                  WHERE gu.user_id = u.id
                    AND gu.group_id = g.id
                    AND u.validated = 1)

Results (groups 1 and 3 have users that have been validated and not validated):

1, 3