I’m running this pretty complicated query to generate a table of user info on the site, but for some reason not all members are being displayed.
SELECT DISTINCT
exp_members.member_id,
exp_member_data.m_field_id_1,
email,
username,
group_title,
join_date,
last_visit,
exp_member_data.m_field_id_5,
exp_channel_titles.author_id,
(SELECT COUNT(exp_channel_titles.title) FROM exp_channel_titles WHERE channel_id = 2 AND exp_channel_titles.author_id = exp_members.member_id) as campaign_count,
(SELECT COUNT(exp_channel_titles.title) FROM exp_channel_titles INNER JOIN exp_channel_data ON exp_channel_titles.entry_id=exp_channel_data.entry_id WHERE exp_channel_titles.channel_id = 2 AND exp_channel_titles.author_id = exp_members.member_id AND field_id_26 = 'active') as campaign_count_active,
(SELECT COUNT(exp_channel_titles.title) FROM exp_channel_titles INNER JOIN exp_channel_data ON exp_channel_titles.entry_id=exp_channel_data.entry_id WHERE exp_channel_titles.channel_id = 2 AND exp_channel_titles.author_id = exp_members.member_id AND field_id_26 = 'inactive') as campaign_count_inactive
FROM exp_member_data
INNER JOIN exp_members ON exp_member_data.member_id=exp_members.member_id
INNER JOIN exp_channel_titles ON exp_members.member_id=exp_channel_titles.author_id
INNER JOIN exp_member_groups ON exp_members.group_id=exp_member_groups.group_idIf I remove these parts relating to the channel entries it works OK:
exp_channel_titles.author_id,
(SELECT COUNT(exp_channel_titles.title) FROM exp_channel_titles WHERE channel_id = 2 AND exp_channel_titles.author_id = exp_members.member_id) as campaign_count,
(SELECT COUNT(exp_channel_titles.title) FROM exp_channel_titles INNER JOIN exp_channel_data ON exp_channel_titles.entry_id=exp_channel_data.entry_id WHERE exp_channel_titles.channel_id = 2 AND exp_channel_titles.author_id = exp_members.member_id AND field_id_26 = 'active') as campaign_count_active,
(SELECT COUNT(exp_channel_titles.title) FROM exp_channel_titles INNER JOIN exp_channel_data ON exp_channel_titles.entry_id=exp_channel_data.entry_id WHERE exp_channel_titles.channel_id = 2 AND exp_channel_titles.author_id = exp_members.member_id AND field_id_26 = 'inactive') as campaign_count_inactiveINNER JOIN exp_channel_titles ON exp_members.member_id=exp_channel_titles.author_idCan anyone see anything obvious that might be filtering some info out?