Custom Report: Get Email Addresses Of Users Of A Service Group

[André P.]

Hi,

 

It would take me an hour or so to create a working SQL query, so I tought I better ask this here.

 

Can you help me out? I am trying to get all email addresses of active users of the group "vServers".

 

Thank you guys!

 

André

[Tyson]

This will get you more info for context, or you can remove the fields just for the email addresses:

SELECT `contacts`.`client_id`, `contacts`.`id` AS `contact_id`, `contacts`.`first_name`, `contacts`.`last_name`, `contacts`.`email`, `services`.`status`, `services`.`date_canceled`, COUNT(`services`.`id`) AS `number_of_services`
FROM `contacts`
INNER JOIN `services` ON `services`.`client_id` = `contacts`.`client_id`
WHERE `services`.`status` = 'active'
AND `services`.`package_group_id` = '1'
GROUP BY `contacts`.`id`
ORDER BY `contacts`.`client_id`, `contacts`.`id`

[André P.]

Sharing is caring, folks!

select distinct email from contacts where client_id in (select client_id from services where package_group_id='n' and status='active');

Where n is your package group # of service XY.

 

I think it would be even better to also get status "cancelled" and check if the date is in the future.

[André P.]

Thank you!