SELECT     Count(DISTINCT a.accountid) AS count
FROM       account                     AS a
LEFT JOIN  media                       AS m
ON         m.mediaid = a.photomediaid
INNER JOIN rolemapping AS rm
ON         rm.principalid = a.accountid
INNER JOIN role AS r
ON         r.id = rm.roleid
INNER JOIN accountdepartment AS ad
ON         ad.accountid = a.accountid
INNER JOIN department AS d
ON         ad.departmentid = d.departmentid
INNER JOIN
           (
                      SELECT     d2.organizationid
                      FROM       accountdepartment AS ad
                      INNER JOIN department        AS d2
                      ON         d2.departmentid = ad.departmentid
                      WHERE      ad.accountid = 71941 ) AS ao
ON         ao.organizationid = d.organizationid
WHERE      a.isdeleted = 0
AND        r.NAME = 'PrimaryCareProviderAdmin'
AND        (
                      a.createdby = 71941
           OR         a.createdby IN
                      (
                                SELECT    a.accountid
                                FROM      account a
                                LEFT JOIN accountdepartment AS ad
                                ON        ad.accountid = a.accountid
                                LEFT JOIN department d
                                ON        d.departmentid = ad.departmentid
                                WHERE     d.organizationid IN
                                          (
                                                 SELECT o.organizationid
                                                 FROM   accountdepartment ad
                                                 JOIN   department d
                                                 ON     d.departmentid = ad.departmentid
                                                 JOIN   organization o
                                                 ON     o.organizationid = d.organizationid
                                                 WHERE  ad.accountid = 71941)
                                OR        a.createdby IN
                                          (
                                                 SELECT a.accountid
                                                 FROM   account a
                                                 JOIN   accountdepartment AS ad
                                                 ON     ad.accountid = a.accountid
                                                 JOIN   department d
                                                 ON     d.departmentid = ad.departmentid
                                                 WHERE  d.organizationid IN
                                                        (
                                                               SELECT o.organizationid
                                                               FROM   accountdepartment ad
                                                               JOIN   department d
                                                               ON     d.departmentid = ad.departmentid
                                                               JOIN   organization o
                                                               ON     o.organizationid = d.organizationid
                                                               WHERE  ad.accountid = 71941))) order BY a.firstname, a.lastnameSELECT DISTINCT a.accountid,
                           a.username,
                           a.email,
                           a.firstname,
                           a.lastname,
                           a.suffix,
                                           concat(a.firstname,IF(a.middleinitial IS NOT NULL, Concat(' ', a.middleinitial, ' '), ' '), a.lastname, IF(a.suffix IS NOT NULL, concat(' ', a.suffix), '')) AS fullname,
                           a.gender,
                           a.birthdate,
                           a.postalcode,
                           a.created,
                           m.smallurl,
                           min(de.accountid) AS primaryaccountid
           FROM            account           AS a
           LEFT JOIN       media             AS m
           ON              m.mediaid = a.photomediaid
           INNER JOIN      rolemapping AS rm
           ON              rm.principalid = a.accountid
           INNER JOIN      role AS r
           ON              r.id = rm.roleid
           INNER JOIN      accountdepar tment AS ad
           ON              ad.accountid = a.accountid
           INNER JOIN      department AS d
           ON              ad.departmentid = d.departmentid
           LEFT JOIN       dependent AS de
           ON              de.dependentaccountid = a.accountid
           LEFT JOIN       account acd
           ON              acd.accountid = de.accountid
           INNER JOIN
                           (
                                      SELECT     d2.organizationid
                                      FROM       accountdepartment AS ad
                                      INNER JOIN department        AS d2
                                      ON         d2.departmentid = ad.departmentid
                                      WHERE      ad.accountid = 71941 ) AS ao
           ON              ao.organizationid = d.organizationid
           WHERE           a.isdeleted = 0
           AND             ifnull(acd.isdeleted, 0) = 0
           AND             r.NAME = 'PrimaryCareProviderAdmin'
           AND             (
                                           a.createdby = 71941
                           OR              a.createdby IN
                                           (
                                                     SELECT    a.accountid
                                                     FROM      account a
                                                     LEFT JOIN accountdepartment AS ad
                                                     ON        ad.accountid = a.accountid
                                                     LEFT JOIN department d
                                                     ON        d.departmentid = ad.departmentid
                                                     WHERE     d.organizationid IN
                                                               (
                                                                      SELECT o.organizationid
                                                                      FROM   accountdepartment ad
                                                                      JOIN   department d
                                                                      ON     d.departmentid = ad.departmentid
                                                                      JOIN   organization o
                                                                      ON     o.organizationid = d.organizationid
                                                                      WHERE  ad.accountid = 71941)
                                                     OR        a.createdby IN
                                                               (
                                                                      SELECT a.accountid
                                                                      FROM   account a
                                                                      JOIN   accountdepartment AS ad
                                                                      ON     ad.accountid = a.accountid
                                                                      JOIN   department d
                                                                      ON     d.departmentid = ad.departmentid
                                                                      WHERE  d.organizationid IN
                                                                             (
                                                                                    SELECT o.organizationid
                                                                                    FROM   accountdepartment ad
                                                                                    JOIN   department d
                                                                                    ON     d.departmentid = ad.departmentid
                                                                                    JOIN   organization o
                                                                                    ON     o.organizationid = d.organizationid
                                                                                    WHERE  ad.accountid = 71941))) GROUP BY a.accountid ORDER BY `accountid` ASC limit 100, 0