MYSQL Query not returning expected records

Question

I have 3 tables as shown below:

subscription_plans

enter image description here

subscribed_videos

enter image description here

subscription_groups

enter image description here

I want to find the plans corresponding to the selected video (table subscription_plans). Table subscription_groups contains the plan-plan mapping ie, a plan can be part of another plan. For example, Plan A, Plan B and Plan C. Plan A contains both Plan B an Plan C and video with id 5 is part of Plan B (table subscribed_videos) , then the query for finding the plans corresponding to video 5 should return Plan A along with Plan B. Also the records should be distinct.

I tried with the following query. But if I am adding a plan which don't have an entry in subscription_groups, then that record is not returning with this query.

$videoid=$_POST['videoid'];
echo "select sv.plan_id,sg.plan_id,sp.id,sp.plan,sp.days_limit,sp.rate from subscribed_videos as sv 
    LEFT JOIN subscription_groups as sg ON sv.plan_id=sg.assosiated_plan_id INNER JOIN 
    subscription_plans as sp ON sv.plan_id=sp.id where sg.assosiated_plan_id=sv.plan_id and sv.videoid=$videoid
    and sg.assosiated_plan_id=sp.id";

Can anyone help me to find the appropriate query for this ? Thanks in advance.

Jenz · Accepted Answer · 2014-05-03 06:23:09Z

1

I think subqueries make sense for this query. The first subquery selects the id of the plan of your video and the second subquery selects the ids of the associated plans.

SELECT 
    sp.id,
    sp.plan,
    sp.days_limit,
    sp.rate 
FROM 
    subscription_plans sp
WHERE 
    id IN (SELECT DISTINCT plan_id FROM subscribed_videos sv where sv.videoid = $videoid)
    OR id IN (SELECT DISTINCT assosiated_plan_id 
        FROM subscription_groups sg
        JOIN subscribed_videos sv ON sv.plan_id = sg.plan_id
        WHERE sv.videoid = $videoid)

edited May 3, 2014 at 6:23

Jenz

8,3718 gold badges50 silver badges83 bronze badges

answered May 3, 2014 at 5:57

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jenz Over a year ago

@Fuzzy..It is showing error Subquery returns more than 1 row

FuzzyTree Over a year ago

@Jenz updated the query to allow multiple rows in the subquery (IN instead of '=')

Collectives™ on Stack Overflow

MYSQL Query not returning expected records

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related