ThaiJo2 active

active.sql

SELECT
journals.journal_id,
journals.path,
journals.enabled,

(SELECT  journal_settings.setting_value FROM journal_settings WHERE journal_id = journals.journal_id AND setting_name = 'name' AND locale = journals.primary_locale) AS name,

(SELECT COUNT(issues.issue_id) FROM issues WHERE journal_id = journals.journal_id AND issues.published = 1) AS issue_count,

(SELECT MAX(issues.date_published) FROM issues WHERE journal_id = journals.journal_id) AS last_published,

(SELECT

MAX(users.date_last_login) AS last_login

FROM users

WHERE user_id IN 
(SELECT
user_user_groups.user_id

FROM user_user_groups

INNER JOIN user_groups ON user_groups.user_group_id = user_user_groups.user_group_id AND user_groups.role_id = 16 AND user_groups.is_default = 1 
INNER JOIN user_group_settings ON user_group_settings.user_group_id = user_groups.user_group_id AND user_group_settings.locale = 'en_US' AND  user_group_settings.setting_value IN ('JE','JM')

WHERE 
  user_groups.context_id = journals.journal_id
)

AND user_id NOT IN (1,2) ) AS last_login

FROM 

journals