Custom SQL Statement to use in SQL to Excel Pro

7 years 10 months ago #179184 by waynejb
Since there is no clean field to pull latest subscription record for a CB user I am trying to pull the MAX record ID for a user to get actual status. Below is my query. Can someone help?

SELECT
`jos_comprofiler`.`firstname` AS `First Name`
, `jos_comprofiler`.`lastname` AS `Last Name`
, `jos_comprofiler`.`cb_address` AS `Address`
, `jos_comprofiler`.`cb_city` AS `City`
, `jos_comprofiler`.`cb_state` AS `State`
, `jos_comprofiler`.`cb_zipcode` AS `Zip`
, `jos_comprofiler`.`cb_phone` AS `Phone Number`
, `jos_users`.`email` AS `Email`
, `jos_comprofiler`.`cb_type` AS `Type`
, `jos_cbsubs_subscriptions`.`status` AS `Status (A=Active, X=Expired)`
, DATE_FORMAT(`jos_cbsubs_subscriptions`.`expiry_date`,'%m-%d-%Y') AS `Expires`
, DATE_FORMAT(`jos_comprofiler`.`lastupdatedate`,'%m-%d-%Y') AS `Last Updated`
FROM
`tastersdb`.`jos_users`
INNER JOIN `tastersdb`.`jos_comprofiler`
ON (`jos_users`.`id` = `jos_comprofiler`.`user_id`)
INNER JOIN `tastersdb`.`jos_cbsubs_subscriptions`
ON (`jos_users`.`id` = `jos_cbsubs_subscriptions`.`user_id`)
WHERE (`jos_comprofiler`.`cb_chapterhome` =7 OR `jos_comprofiler`.`cb_chapteraddlist` LIKE '%07%')
AND `jos_cbsubs_subscriptions`.`id` IN (SELECT MAX(`jos_cbsubs_subscriptions`.`id`) FROM `jos_cbsubs_subscriptions`)
ORDER BY `Type` ASC;
Moderators: beatnantkrileon
Time to create page: 0.354 seconds
Facebook Twitter Google LinkedIn