[SOLVED] Display names of users who have used a family plan coupon?

I'm implementing the "Coupon Generation for Family Plan Subscription" described in www.joomlapolis.com/support/tutorials/107-use-cases/18407-purchasing-discount-coupons

This is being done for a teaching association, where the "parent" subscriber wants to know who has used the coupons.

The code below lets them see the user IDs of people who have used the coupons, but not their names.

SELECT p.`coupon_code`, p.`max_uses_total`, ( SELECT COUNT(*) FROM `#__cbsubs_promotions_uses` AS c WHERE c.`promotion_id` = p.`id` ) AS count, ( SELECT group_concat(d.`user_id` separator ', ') FROM `#__cbsubs_promotions_uses` AS d WHERE d.`promotion_id` = p.`id` ) AS idlist FROM `#__cbsubs_promotions` AS p WHERE p.`coupon_code` REGEXP '^[user_id]-'

How can I change this code to get the table to show either the coupon user's name (I guess from users) or the "name" info from cbsubs_promotions (which is probably better as it includes their name, email address and subscription date)?

Thanks, Peter

(Community Builder 1.9.1, CBSubs 3.0.0, Joomla 2.5.25)

peterbatchelor wrote: I'm implementing the "Coupon Generation for Family Plan Subscription" described in www.joomlapolis.com/support/tutorials/107-use-cases/18407-purchasing-discount-coupons

This is being done for a teaching association, where the "parent" subscriber wants to know who has used the coupons.

The code below lets them see the user IDs of people who have used the coupons, but not their names.

SELECT p.`coupon_code`, p.`max_uses_total`, ( SELECT COUNT(*) FROM `#__cbsubs_promotions_uses` AS c WHERE c.`promotion_id` = p.`id` ) AS count, ( SELECT group_concat(d.`user_id` separator ', ') FROM `#__cbsubs_promotions_uses` AS d WHERE d.`promotion_id` = p.`id` ) AS idlist FROM `#__cbsubs_promotions` AS p WHERE p.`coupon_code` REGEXP '^[user_id]-'

How can I change this code to get the table to show either the coupon user's name (I guess from users) or the "name" info from cbsubs_promotions (which is probably better as it includes their name, email address and subscription date)?

Thanks, Peter

(Community Builder 1.9.1, CBSubs 3.0.0, Joomla 2.5.25)

Sorry for the delay.

Yes you would need to basically alter the query to do a join with another table in order to grab the username.

I am not that hot SQL wise, so I have referred this to Kyle or Beat to assist when they get a breather.

Thanks Nick,
I look forward to hearing from them...
Peter

The below sub-query of that usage would need to be adjusted.


The below for example should work.


The above should output a comma separated list of usernames instead of user ids for example. You can change u.`username` to u.`name` for example if you'd like name. It can also be changed to u.`email` for the email address.

Thanks Kyle,
that's perfect for what they need.
Peter