Query Help

7 years 5 months ago #190502 by lynnapp
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
7 years 5 months ago #190536 by lynnapp
I figured it out
to get highest record number (People on my site can only have one membership at a time so it works)

add to the end
GROUP BY c.`user_id`
Moderators: beatnantkrileon
Time to create page: 0.374 seconds