The subquery does not return the expected results

This is the query I have written to get the plans chosen by an user. But this is returning the records in usersubscription table even if the user is not subscribed (if there is no records in the table corresponding to the user).

$userid=$_POST['userid'];
$videoid=$_POST['videoid'];
$subscribedquery=$this->db->query("select id from usersubscription where plan_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) and user_id=$userid");

Below I am sharing the structure of all tables.

CREATE TABLE IF NOT EXISTS `subscribed_videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `videoid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

INSERT INTO `subscribed_videos` (`id`, `plan_id`, `videoid`) VALUES
(7, 2, 1),
(8, 2, 2),
(14, 1, 3),
(15, 1, 4),
(16, 1, 5),
(17, 1, 21),
(18, 1, 28),
(19, 1, 2),
(20, 3, 4),
(21, 3, 6),
(24, 5, 25),
(25, 6, 5);

CREATE TABLE IF NOT EXISTS `subscription_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `assosiated_plan_id` int(11) NOT NULL,
  `added_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `subscription_groups` (`id`, `plan_id`, `assosiated_plan_id`, `added_on`) VALUES
(1, 1, 1, 0),
(2, 2, 2, 0),
(3, 3, 3, 0),
(4, 4, 1, 0),
(5, 4, 2, 0),
(6, 4, 3, 0),
(12, 5, 5, 0),
(13, 5, 1, 0),
(14, 5, 2, 0),
(15, 6, 1, 0);

CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan` varchar(256) NOT NULL,
  `days_limit` int(11) NOT NULL,
  `added_on` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `rate` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `subscription_plans` (`id`, `plan`, `days_limit`, `added_on`, `status`, `rate`) VALUES
(1, 'PlanA', 15, 1398249706, 1, 150.00),
(2, 'PlanB', 15, 1398249679, 1, 100.00),
(3, 'PlanC', 15, 1398249747, 1, 100.00),
(4, 'PlanD', 10, 1398249771, 1, 500.00),
(5, 'PlanE', 15, 1398250104, 1, 200.00),
(6, 'Plan R1', 20, 1398250104, 1, 200.00);

CREATE TABLE IF NOT EXISTS `usersubscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `subscribed_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `usersubscription` (`id`, `user_id`, `plan_id`, `subscribed_on`) VALUES
(1, 1, 1, 1399091458);

Content:

SELECT * FROM subscribed_videos;
+----+---------+---------+
| id | plan_id | videoid |
+----+---------+---------+
|  7 |       2 |       1 |
|  8 |       2 |       2 |
| 14 |       1 |       3 |
| 15 |       1 |       4 |
| 16 |       1 |       5 |
| 17 |       1 |      21 |
| 18 |       1 |      28 |
| 19 |       1 |       2 |
| 20 |       3 |       4 |
| 21 |       3 |       6 |
| 24 |       5 |      25 |
| 25 |       6 |       5 |
+----+---------+---------+

SELECT * FROM subscription_groups;
+----+---------+--------------------+----------+
| id | plan_id | assosiated_plan_id | added_on |
+----+---------+--------------------+----------+
|  1 |       1 |                  1 |        0 |
|  2 |       2 |                  2 |        0 |
|  3 |       3 |                  3 |        0 |
|  4 |       4 |                  1 |        0 |
|  5 |       4 |                  2 |        0 |
|  6 |       4 |                  3 |        0 |
| 12 |       5 |                  5 |        0 |
| 13 |       5 |                  1 |        0 |
| 14 |       5 |                  2 |        0 |
| 15 |       6 |                  1 |        0 |
+----+---------+--------------------+----------+

SELECT * FROM subscription_plans;
+----+---------+------------+------------+--------+--------+
| id | plan    | days_limit | added_on   | status | rate   |
+----+---------+------------+------------+--------+--------+
|  1 | PlanA   |         15 | 1398249706 |      1 | 150.00 |
|  2 | PlanB   |         15 | 1398249679 |      1 | 100.00 |
|  3 | PlanC   |         15 | 1398249747 |      1 | 100.00 |
|  4 | PlanD   |         10 | 1398249771 |      1 | 500.00 |
|  5 | PlanE   |         15 | 1398250104 |      1 | 200.00 |
|  6 | Plan R1 |         20 | 1398250104 |      1 | 200.00 |
+----+---------+------------+------------+--------+--------+

 SELECT * FROM usersubscription
+----+---------+---------+---------------+
| id | user_id | plan_id | subscribed_on |
+----+---------+---------+---------------+
|  1 |       1 |       1 |    1399091458 |
+----+---------+---------+---------------+

Fiddle of same: http://sqlfiddle.com/#!2/90b2e/1

I expect the result to be like this if the user is already subscribed to a plan of the selected video otherwise the query should return empty records:

id
---
1

Also how can I return the records only if the plan is not expired for the user using the query itself. ie, when an user purchases a plan, it will be entered in the usersubscription table. The subscribed_on field will contain the php unix time() in which it is purchased. So I would like to get only the plans corresponding to a user and a video, which is not expired, in this query. The expiry days is stored as days in days_limit field of subscription_plans table (eg: 15).

Can anyone help me to find an appropriate solution for this.

Thanks in advance.

---

I would say you should try this using joins

SELECT DISTINCT s.id ,
FROM_UNIXTIME(p.`added_on`),
DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) `expiry_date`
FROM usersubscription s
LEFT JOIN subscribed_videos v ON (s.plan_id = v.plan_id)
LEFT JOIN subscription_groups g ON(s.id = assosiated_plan_id )
LEFT JOIN subscribed_videos sv ON sv.plan_id = g.plan_id
LEFT JOIN `subscription_plans` p ON (p.id = s.plan_id)
WHERE s.user_id=1 AND  sv.videoid = 5
AND  v.videoid = 5
AND  DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) > CURRENT_DATE()

In above query i have an additional join subscription_plans to check your expiry date condition, also note you are using post variables directly in query i.e $userid=$_POST['userid'];$videoid=$_POST['videoid']; which is not safe and when you are using codeigniter then you should use active record library to build your query which will take of all escaping at its own end

Fiddle Demo

Here is the active record version of above query

$query = $this->db
    ->select('s.id')
    ->distinct()
    ->from('usersubscription s')
    ->join('subscribed_videos v ','s.plan_id = v.plan_id','LEFT')
    ->join('subscription_groups g ','s.id = assosiated_plan_id','LEFT')
    ->join('subscribed_videos sv','sv.plan_id = g.plan_id','LEFT')
    ->join('`subscription_plans` p','p.id = s.plan_id','LEFT')
    ->where('s.user_id',$userid)
    ->where('sv.videoid',$videoid)
    ->where('v.videoid',$videoid)
    ->where('DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) > CURRENT_DATE()',null,FALSE)
    ->get();