Getting count from inner join table

Question

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.

Answer 1

You can do so

SELECT 
  us.plan_id,
  us.subscribed_on,
  sp.plan,
  sp.days_limit,
  sp.rate,
  COUNT(sv.videoid) 
FROM
  usersubscription AS us 
  RIGHT JOIN subscription_plans AS sp 
    ON us.plan_id = sp.id 
  INNER JOIN subscribed_videos AS sv 
    ON sp.id = sv.plan_id 
  INNER JOIN subscription_groups g
    ON(g.plan_id =sv .plan_id OR sv.plan_id= g.assosiated_plan_id)
 WHERE sp.status = 1 
 AND (us.user_id = 1 OR us.user_id IS NULL )

Demo

Since user has only plan associated but the associated plan can also has another plan linked so the last condition will check the user id but for is null to for the second linked plan user id will be null due to right join on subscription_plans

Edit

SELECT 
u.plan_id,
u.subscribed_on,
p.plan,
  p.days_limit,
  p.rate
,COUNT(DISTINCT v.`videoid`) 
 FROM `usersubscription` u
JOIN `subscription_groups` g 
ON (u.`plan_id` = g.`plan_id`)
RIGHT JOIN `subscription_plans` p 
ON(u.`plan_id` = p.`id` OR g.`assosiated_plan_id` = p.`id`)
INNER JOIN `subscribed_videos` v  ON(v.`plan_id`=g.`assosiated_plan_id` OR u.`plan_id`= v.`plan_id`)
WHERE u.`id`=1 AND p.`status` = 1

Demo 1 Demo2

For video ids you can use group_concat

SELECT 
u.plan_id,
u.subscribed_on,
p.plan,
  p.days_limit,
  p.rate
,COUNT(DISTINCT v.`videoid`) `video_count` ,
GROUP_CONCAT(DISTINCT v.`videoid`) `video_ids`
 FROM `usersubscription` u
JOIN `subscription_groups` g 
ON (u.`plan_id` = g.`plan_id`)
RIGHT JOIN `subscription_plans` p 
ON(u.`plan_id` = p.`id` OR g.`assosiated_plan_id` = p.`id`)
INNER JOIN `subscribed_videos` v  ON(v.`plan_id`=g.`assosiated_plan_id` OR u.`plan_id`= v.`plan_id`)
WHERE u.`id`=1 AND p.`status` = 1

Demo 1a Demo 2a