Check if there are users who are using a common password

Question

Table Structure:
userid password adminlevel

• userid is BIGINT (UNIQUE)
• password is VARCHAR
• adminlevel is SMALLINT

How do I write a query to get a list of users with adminlevel greater than 0 whose password is being used on other accounts?

The query result must satisfy the following conditions:

1. adminlevel > 0
2. there are accounts other than itself where the same password is being used

Answer 1

SELECT a.userid AS TheAdmin,
       GROUP_CONCAT(b.userid) AS NonAdminsWithSamePwd
    FROM tbl AS a
    JOIN tbl AS b  ON b.password = a.password
    WHERE a.adminlevel > 0
      AND b.adminlevel = 0