I've been asked to export the all the payment methods for a plan on my site so the committee can make a decision on which gateways to keep... but I am getting lots of duplicate records (I should be getting around 192 results, but am getting 900+
This is my sql - any ideas where I'm going wrong
Code:
SELECT a.`id`
, a.`username`
, c.`plan_id`
, d.`payment_method`
, c.`status`
, c.`subscription_date`
FROM `j25_users` AS a
INNER JOIN `j25_comprofiler` AS b
ON b.`user_id` = a.`id`
INNER JOIN `j25_cbsubs_subscriptions` AS c
ON c.`user_id` = b.`user_id`
INNER JOIN `j25_cbsubs_payments` AS d
ON d.`for_user_id` = c.`user_id`
INNER JOIN `j25_cbsubs_payment_items` AS e
ON e.`payment_basket_id` = d.`payment_basket_id`
WHERE c.`plan_id` = 20
I'm just a user like you, but from many other threads I understand that no one here will give you complete and exact code for custom queries.
From what you're saying it seems that with the "Basket" and "Payments" tables in the CBSubs backend you should get what you need, anyway, depending on what you want to base your report on.
If you want a similar but custom query, turn on debug and see how the backend views are generated, then build your own query based on that?
Indeed, we don't offer MySQL expert query design support within our standard support.
But obviously you are missing a DISTINCT keyword after your SELECT keyword to avoid duplicate results. Sorry, won't be able to help further as it's outside our support scope, but that should go a good way where you probably want it to go.
Thanks Beat... that didn't seem to get the correct of results either - I'd already tried that... maybe the admins have been misusing CB Subs - I'll investigate further
beat
