Hi!
I'm trying to pull a report that has to pull information from 2 tables in the same database- and then be able to enter date range to filter table. From other posts I was trying this but can't get it to work. My user_id is my link between tables.

SELECT
jml_comprofiler.firstname, jml_comprofiler.lastname,
jml_comprofiler.user_id,
jml_cbsubs_subscriptions.expiry_date,
jml_cbsubs_subscriptions.user_id,
FROM
jml_comprofiler.user_id INNER JOIN jml_cbsubs_subscriptions.user_id
ON jml_cbsubs_subscriptions.user_id = jml_cbsubs_subscriptions.user_id
WHERE
({$REQUEST:start|empty:''} = '' OR jml_cbsubs_subscriptions.expiry_date >= {$REQUEST:start|empty:''})
AND
({$REQUEST:End|empty:''} = '' OR jml_cbsubs_subscriptions.expiry_date < {$REQUEST:End|empty:''})

Thanks for any help,
Tasha

Hello,

Try the following SQL query:

SELECT
C.firstname,
C.lastname,
C.user_id,
S.expiry_date,
S.user_id
FROM
jml_comprofiler C INNER JOIN jml_cbsubs_subscriptions S
ON C.user_id = S.user_id
WHERE
({$REQUEST:start|empty:''} = '' OR S.expiry_date >= {$REQUEST:start|empty:''})
AND
({$REQUEST:End|empty:''} = '' OR S.expiry_date < {$REQUEST:End|empty:''})

Regards,
ARI Soft

Thank you! Thank you! Thank you!
If I could leave another review on how awesome ARI data and their support is in Joomla! extensions I would! Thanks, Tasha

I would like to add a third table to my query now
I tired the inner join but I get an inner join error on line 14 (which is the P.id line).
Any ideas?

SELECT
C.cb_businessname,
C.firstname,
C.lastname,
C.cb_subs_inv_address_street,
C.cb_subs_inv_address_city,
C.cb_subs_inv_address_state,
C.cb_subs_inv_address_zip,
C.cb_phone,
C.user_id,
S.expiry_date,
S.plan_id,
S.user_id
P.id
FROM
jml_comprofiler C INNER JOIN jml_cbsubs_subscriptions S
ON C.user_id = S.user_id
INNER JOIN jml_cbsubs_subscriptions S
ON P.id = S.plan_id
WHERE
({$REQUEST:start|empty:''} = '' OR S.expiry_date >= {$REQUEST:start|empty:''})
AND
({$REQUEST:End|empty:''} = '' OR S.expiry_date < {$REQUEST:End|empty:''})

Try the following query:



Regards,
ARI Soft

I couldn't get that to work. I think that I forgot to call the 3rd table which is jml_cbsubs_plans.
So I have
jml_comprofiler = C
jml_cbsubs_subscriptions = S
jml_cbsubs_plans = P

Basically what I'm trying to do is pull user info from comprofiler, subscription information from cbsubs_subscription, and then the cbsubs_subscription had a plan_id that is a number - I need to pull the text wording for cbsubs_plans associated with the plan_id . This way the user sees the wording for the plan, not just a number that makes no sense to them.
Thank you so much for your time and help!
I appreciate it,
Tasha

SELECT
C.cb_businessname,
C.firstname,
C.lastname,
C.cb_subs_inv_address_street,
C.cb_subs_inv_address_city,
C.cb_subs_inv_address_state,
C.cb_subs_inv_address_zip,
C.cb_phone,
C.user_id,
S.expiry_date,
S.plan_id,
S.user_id,
P.id
FROM
jml_comprofiler C INNER JOIN jml_cbsubs_subscriptions S
ON C.user_id = S.user_id
jml_cbsubs_plans P INNER JOIN jml_cbsubs_subscriptions S
ON P.id = S.plan_id
WHERE
({$REQUEST:start|empty:''} = '' OR S.expiry_date >= {$REQUEST:start|empty:''})
AND
({$REQUEST:End|empty:''} = '' OR S.expiry_date < {$REQUEST:End|empty:''})