I have tables as described below:
subscription_plans (Table for storing all plans)
id plan days_limit added_on status rate
------------------------------------------------
1 PlanA 15 1398249706 1 150.00
2 PlanB 15 1398249706 1 150.00
subscribed_videos (Table for storing details of video in each plans)
id plan_id videoid
----------------------
1 1 1
2 2 2
subscription_groups (Table for storing groups where a plan can be part of another plan. ie, Plan A be a plan with 2 other individual plans, Plan B and C )
id plan_id assosiated_plan_id added_on
----------------------------------------------
1 1 2 1398249706
usersubscription (Table for storing user subscribed plans)
id user_id plan_id subscribed_on
---------------------------------------
1 1 1 1398771106
Now, my problem is that how can I get the count of videos for each plans. If Plan A contains both Plan B and C (subscription_groups table), then the count should return the total video count for each individual plans in that particular plan. Now I have done with a query which will return plan details along with count of videos for a plan but I am not able to join it with subscription_groups. How can I accomplish this in a single query.
$data['planquery']=$this->db->query("select
us.plan_id,us.subscribed_on,sp.plan,sp.days_limit,sp.rate,count(sv.videoid) from
usersubscription as us INNER JOIN
subscription_plans as sp ON us.plan_id=sp.id INNER JOIN subscribed_videos as sv ON sp.id=sv.plan_id where sp.status=1 and us.user_id=1");
Expected Result:
plan_id subscribed_on plan days_limit rate count
-------------------------------------------------------
1 1398771106 PlanA 15 150.00 2
Can anyone help me to find a solution for this?
Thanks in advance.