Hi,
I have the query that I got somewhere on this site but cant't find it to continue the tread
This will select All subs with status as text
SELECT a.`id` AS ID
, a.`name` AS Name
, a.`username` AS Username
, a.`email` AS Email
, ( CASE c.`status` WHEN 'A' THEN 'Active' WHEN 'R' THEN 'Unpaid' WHEN 'X' THEN 'Expired' WHEN 'C' THEN 'Unsubscribed' WHEN 'U' THEN 'Upgrade' WHEN 'I' THEN 'Invalid' ELSE 'Unknown' END ) AS Status
, c.`plan_id` AS Subscription
, d.`name` AS Plan
, c.`subscription_date` AS Subscribed
, c.`last_renewed_date` AS Renewed
, c.`expiry_date` AS Expires
FROM `jos_users` AS a
INNER JOIN `jos_comprofiler` AS b
ON b.`user_id` = a.`id`
INNER JOIN `jos_cbsubs_subscriptions` AS c
ON c.`user_id` = b.`user_id`
INNER JOIN `jos_cbsubs_plans` AS d
ON d.`id` = c.`plan_id`
What I need is to limit this so when a person has multiple subs (has renewed or changed )
so if jos_cbsubs_subscriptions` AS c has more than one with userid of X
then it will only show the highest ID of jos_cbsubs_subscriptions (or the one with the last renewal date the highest
I am sure there is a way but not sure
Thank you