MySQL return column if username part of disabled group

Question

I have two tables as below

radcheck

+----+-------------------+--------------------+----+--------+
| id | username          | attribute          | op | value  |
+----+-------------------+--------------------+----+--------+
|  1 | userA             | Cleartext-Password | := | Apass  |
|  2 | userB             | Cleartext-Password | := | Bpass  |
|  3 | DC:9F:DB:xx:xx:xx | Auth-Type          | := | Accept |
|  4 | userC             | Cleartext-Password | := | Cpass  |
+----+-------------------+--------------------+----+--------+

radusergroup

+----------+------------+----------+
| username | groupname  | priority |
+----------+------------+----------+
| userA    | daily-plan |        1 |
| userA    | disabled   |        0 |
| userB    | quota-plan |        1 |
| userC    | disabled   |        0 |
| userC    | try        |        1 |
+----------+------------+----------+

I use the below query to return results which lists usernames that are not part of disabled group but i would like to return another column in results called disabled with value as 1 if part of disabled group and 0 if not:

SELECT c.id, c.username, c.value, g.groupname
FROM radcheck c LEFT JOIN
     radusergroup g
     USING (username)
WHERE attribute = 'Cleartext-Password' AND
      groupname <> 'disabled';

I tried multiple ways using triple left joins using below query but they dont seem to work, the groupname column in result is always of the first groupname found in radusergroup table:

SELECT c.id, c.username, c.value, g.groupname, (disabled.username IS NOT NULL) AS disabled
FROM radcheck c LEFT JOIN
     radusergroup g
     ON c.username = g.username LEFT JOIN
     radusergroup disabled
     ON disabled.username = c.username AND
        disabled.groupname = 'disabled'
WHERE (c.username = g.username) AND
      attribute = 'Cleartext-Password'
GROUP BY c.username;

the above outputs:

+----+----------+-------+------------+----------+
| id | username | value | groupname  | disabled |
+----+----------+-------+------------+----------+
|  1 | userA    | Apass | daily-plan |        1 |
|  2 | userB    | Bpass | quota-plan |        0 |
|  4 | userC    | Cpass | disabled   |        1 |
+----+----------+-------+------------+----------+

Answer 1

The simplest solution is to extend your query with an EXISTS and a correlated subquery.

SELECT c.id,
       c.username,
       c.value,
       g.groupname,
       EXISTS (SELECT *
                      FROM radusergroup g2
                           WHERE g2.username = c.username
                                 AND g2.groupname = 'disabled') disabled
       FROM radcheck c
            LEFT JOIN radusergroup g
                      ON g.username = c.username
       WHERE c.attribute = 'Cleartext-Password'
             AND g.groupname <> 'disabled';

That assumes, that your first query is fine. I couldn't tell from your post if it is OK, since it will return a row for each non disabled group a user is in, i.e. probably more than one row per user. If that is an issue too, you can use a second EXISTS in an analog way with g2.groupname <> 'disabled' and removing the left join.

---

Edit: If you only want flags if a user in a disabled and/or a not disabled group:

SELECT c.id,
       c.username,
       c.value,
       EXISTS (SELECT *
                      FROM radusergroup g2
                           WHERE g2.username = c.username
                                 AND g2.groupname = 'disabled') disabled,
       EXISTS (SELECT *
                      FROM radusergroup g2
                           WHERE g2.username = c.username
                                 AND g2.groupname <> 'disabled') not_disabled
       FROM radcheck c
       WHERE c.attribute = 'Cleartext-Password';