How to specify the parent query field from within a subquery in mySQL?

Is there a way to specify the parent query field from within a subquery in mySQL?

For Example:
I have written a basic Bulletin Board type program in PHP.

In the database each post contains: id(PK) and parent_id(the id of the parent post). If the post is itself a parent, then its parent_id is set to 0.

I am trying to write a mySQL query that will find every parent post and the number of children that the parent has.

$query = "SELECT id, (
      SELECT COUNT(1) 
      FROM post_table 
      WHERE parent_id = id
) as num_children
FROM post_table
WHERE parent_id = 0";

The tricky part is that the first id doesn't know that it should be referring to the second id that is outside of the subquery. I know that I can do SELECT id AS id_tmp and then refer to it inside the subquery, but then if I want to also return the id and keep "id" as the column name, then I'd have to do a query that returns me 2 columns with the same data (which seems messy to me)

$query = "SELECT id, id AS id_tmp, 
            (SELECT COUNT(1)
            FROM post_table
            WHERE parent_id = id_tmp) as num_children
         FROM post_table
         WHERE parent_id = 0";

The messy way works fine, but I feel an opportunity to learn something here so I thought I'd post the question.

 Answers

How about:

$query = "SELECT p1.id, 
                 (SELECT COUNT(1) 
                    FROM post_table p2 
                   WHERE p2.parent_id = p1.id) as num_children
            FROM post_table p1
           WHERE p1.parent_id = 0";

or if you put an alias on the p1.id, you might say:

$query = "SELECT p1.id as p1_id, 
                 (SELECT COUNT(1) 
                    FROM post_table p2 
                   WHERE p2.parent_id = p1.id) as num_children
            FROM post_table p1
           WHERE p1.parent_id = 0";

---

Give the tables unique names:

$query = "SELECT a.id, (SELECT COUNT(1) FROM post_table b WHERE parent_id = a.id) as num_children FROM post_table a WHERE a.parent_id = 0";

---

Thanks Don. I had a nested query as shown below and a WHERE clause in it wasn't able to determine alias v1. Here is the code which isn't working:

Select 
    teamid,
    teamname
FROM
    team as t1
INNER JOIN (
    SELECT 
        venue_id, 
        venue_scores, 
        venue_name 
    FROM venue 
    WHERE venue_scores = (
        SELECT 
            MAX(venue_scores) 
        FROM venue as v2 
        WHERE v2.venue_id = v1.venue_id      /* this where clause wasn't working */
    ) as v1    /* v1 alias already present here */
);

So, I just added the alias v1 again inside the JOIN. Which made it work.

Select 
    teamid,
    teamname
FROM
    team as t1
INNER JOIN (
    SELECT 
        venue_id, 
        venue_scores, 
        venue_name 
    FROM venue as v1              /* added alias v1 here again */
    WHERE venue_scores = (
        SELECT 
            MAX(venue_scores) 
        FROM venue as v2 
        WHERE v2.venue_id = v1.venue_id   /* Now this works!! */
    ) as v1     /* v1 alias already present here */
);

Hope this will be helpful for someone.

Mysql: Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query and I hate it, I don't know why.

I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a JOIN and therefore there is nothing to worry about?

 Answers

Taken from the manual (13.2.10.11 Rewriting Subqueries as Joins):

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

So subqueries can be slower than LEFT [OUTER] JOINS, but in my opinion their strength is slightly higher readability.

---

In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.

The good thing in sub-queries is that they are more readable than JOINs: that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.

---

First of all, to compare the two first you should distinguish queries with subqueries to:

1. a class of subqueries that always have corresponding equivalent query written with joins
2. a class of subqueries that can not be rewritten using joins

For the first class of queries a good RDBMS will see joins and subqueries as equivalent and will produce same query plans.

These days even mysql does that.

Still, sometimes it does not, but this does not mean that joins will always win - I had cases when using subqueries in mysql improved performance. (For example if there is something preventing mysql planner to correctly estimate the cost and if the planner doesn't see the join-variant and subquery-variant as same then subqueries can outperform the joins by forcing a certain path).

Conclusion is that you should test your queries for both join and subquery variants if you want to be sure which one will perform better.

For the second class the comparison makes no sense as those queries can not be rewritten using joins and in these cases subqueries are natural way to do the required tasks and you should not discriminate against them.

---

I think what has been under-emphasized in the cited answers is the issue of duplicates and problematic results that may arise from specific (use) cases.

(although Marcelo Cantos does mention it)

I will cite the example from Stanford's Lagunita courses on SQL.

Student Table

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  123 | Amy    |  3.9 |   1000 |
|  234 | Bob    |  3.6 |   1500 |
|  345 | Craig  |  3.5 |    500 |
|  456 | Doris  |  3.9 |   1000 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  987 | Helen  |  3.7 |    800 |
|  876 | Irene  |  3.9 |    400 |
|  765 | Jay    |  2.9 |   1500 |
|  654 | Amy    |  3.9 |   1000 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

Apply Table

(applications made to specific universities and majors)

+------+----------+----------------+----------+
| sID  | cName    | major          | decision |
+------+----------+----------------+----------+
|  123 | Stanford | CS             | Y        |
|  123 | Stanford | EE             | N        |
|  123 | Berkeley | CS             | Y        |
|  123 | Cornell  | EE             | Y        |
|  234 | Berkeley | biology        | N        |
|  345 | MIT      | bioengineering | Y        |
|  345 | Cornell  | bioengineering | N        |
|  345 | Cornell  | CS             | Y        |
|  345 | Cornell  | EE             | N        |
|  678 | Stanford | history        | Y        |
|  987 | Stanford | CS             | Y        |
|  987 | Berkeley | CS             | Y        |
|  876 | Stanford | CS             | N        |
|  876 | MIT      | biology        | Y        |
|  876 | MIT      | marine biology | N        |
|  765 | Stanford | history        | Y        |
|  765 | Cornell  | history        | N        |
|  765 | Cornell  | psychology     | Y        |
|  543 | MIT      | CS             | N        |
+------+----------+----------------+----------+

Let's try to find the GPA scores for students that have applied to CS major (regardless of the university)

Using a subquery:

select GPA from Student where sID in (select sID from Apply where major = 'CS');

+------+
| GPA  |
+------+
|  3.9 |
|  3.5 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

The average value for this resultset is:

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');

+--------------------+
| avg(GPA)           |
+--------------------+
| 3.6800000000000006 |
+--------------------+

Using a join:

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+------+
| GPA  |
+------+
|  3.9 |
|  3.9 |
|  3.5 |
|  3.7 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

average value for this resultset:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+-------------------+
| avg(GPA)          |
+-------------------+
| 3.714285714285714 |
+-------------------+

It is obvious that the second attempt yields misleading results in our use case, given that it counts duplicates for the computation of the average value. It is also evident that usage of distinct with the join - based statement will not eliminate the problem, given that it will erroneously keep one out of three occurrences of the 3.9 score. The correct case is to account for TWO (2) occurrences of the 3.9 score given that we actually have TWO (2) students with that score that comply with our query criteria.

It seems that in some cases a sub-query is the safest way to go, besides any performance issues.

---

MySQL version: 5.5.28-0ubuntu0.12.04.2-log

I was also under the impression that JOIN is always better than a sub-query in MySQL, but EXPLAIN is a better way to make a judgment. Here is an example where sub queries work better than JOINs.

Here is my query with 3 sub-queries:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

EXPLAIN shows:

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| id | select_type        | table    | type   | possible_keys                                       | key          | key_len | ref                                             | rows | Extra                    |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
|  1 | PRIMARY            | vrl      | index  | PRIMARY                                             | moved_date   | 8       | NULL                                            |  200 | Using where              |
|  1 | PRIMARY            | l        | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              |
|  1 | PRIMARY            | vrlih    | eq_ref | PRIMARY                                             | PRIMARY      | 9       | ranker.vrl.list_id,ranker.vrl.ontology_id,const |    1 | Using where              |
|  1 | PRIMARY            | lbs      | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              |
|  4 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

The same query with JOINs is:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43 
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55 
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

and the output is:

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                       | key          | key_len | ref                                         | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | lt3   | ref    | list_tag_key,list_id,tag_id                         | tag_id       | 5       | const                                       | 2386 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.lt3.list_id                          |    1 | Using where                                  |
|  1 | SIMPLE      | vrlih | ref    | PRIMARY                                             | PRIMARY      | 4       | ranker.lt3.list_id                          |  103 | Using where                                  |
|  1 | SIMPLE      | vrl   | ref    | PRIMARY                                             | PRIMARY      | 8       | ranker.lt3.list_id,ranker.vrlih.ontology_id |   65 | Using where                                  |
|  1 | SIMPLE      | lt1   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index; Not exists         |
|  1 | SIMPLE      | lbs   | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                          |    1 | Using where                                  |
|  1 | SIMPLE      | lt2   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index                     |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

A comparison of the rows column tells the difference and the query with JOINs is using Using temporary; Using filesort.

Of course when I run both the queries, the first one is done in 0.02 secs, the second one does not complete even after 1 min, so EXPLAIN explained these queries properly.

If I do not have the INNER JOIN on the list_tag table i.e. if I remove

AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL  

from the first query and correspondingly:

INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

from the second query, then EXPLAIN returns the same number of rows for both queries and both these queries run equally fast.

---

Subqueries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword. They are allowed at nearly any meaningful point in a SQL statement, including the target list, the WHERE clause, and so on. A simple sub-query could be used as a search condition. For example, between a pair of tables:

   SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

Note that using a normal value operator on the results of a sub-query requires that only one field must be returned. If you're interested in checking for the existence of a single value within a set of other values, use IN:

   SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');

This is obviously different from say a LEFT-JOIN where you just want to join stuff from table A and B even if the join-condition doesn't find any matching record in table B, etc.

If you're just worried about speed you'll have to check with your database and write a good query and see if there's any significant difference in performance.

---

Taken directly from essentialsql.com:

Joins and subqueries are both used to combine data from different tables into a single result. They share many similarities and differences.Subqueries can be used to return either a scalar (single) value or a row set; whereas, joins are used to return rows.A common use for a subquery may be to calculate a summary value for use in a query. For instance we can use a subquery to help us obtain all products have a greater than average product price. For example:

SELECT ProductID,
       Name,
       ListPrice,
       (SELECT AVG(ListPrice)
        FROM Production.Product
       ) AS AvgListPrice
FROM Production.Product
WHERE ListPrice > (SELECT AVG(ListPrice)
                   FROM Production.Product
                  )

There are two subqueries in this SELECT statement. The first’s purpose is to display the average list price of all products, the second’s purpose is for filtering out products less than or equal to the average list price. Contrast this with a join whose main purpose of a join is to combine rows from one or more tables based on a match condition. For example we can use a join display product names and models.

SELECT Product.Name,
       ProductModel.Name AS ModelName
FROM Production.product
INNER JOIN Production.ProductModel
ON Product.ProductModelID = ProductModel.ProductModelID

In this statement we’re using an INNER JOIN to match rows from both the Product and ProductModel tables. Notice that the column ProducModel.Name is available for use throughout the query.The combined row set is then available by the select statement for use to display, filter, or group by the columns.This is different than the subquery. There the subquery returns a result, which is immediately used.Note that he join is an integral part of the select statement. It can not stand on its own as a subquery can.

---

The difference is only seen when the second joining table has significantly more data than the primary table. I had an experience like below...

We had a users table of one hundred thousand entries and their membership data (friendship) about 3 hundred thousand entries. It was a join statement in order to take friends and their data, but with a great delay. But it was working fine where there was only a small amount of data in the membership table. Once we changed it to use a sub-query it worked fine.

But in the mean time the join queries are working with other tables that have fewer entries than the primary table.

So I think the join and sub query statements are working fine and it depends on the data and the situation.

Optimize the MySQL query with a dependent subquery

I need to find a way to eliminate a dependent sub-query.

I have a table of articles that can have multiple languages. The simplified table structure is as follows:

id, title, language, translation_set_id

1 A    en 0
2 B    en 2
3 B_ru ru 2
4 C    en 4
5 C_ru ru 4
6 D    en 6
7 D_fr fr 6

The translation_set_id is 0 when an article doesn't have translations, or is set to the id of the base translation. So B is the original English article, and B_ru is the Russian translation of the article.

I need a query that would allow me to return all Russian articles, or if they don't exist the original language articles. So it would return.

1 A    en 0
3 B_ru ru 2
5 C_ru ru 4
6 D    en 6

So far I have this:

SELECT id, title, language, translation_set_id
FROM articles a
WHERE
  a.translation_set_id = 0
  OR (a.language = 'ru')
  OR (a.id = a.translation_set_id AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id
            AND ac.language = 'ru')
     )

But this executes the sub-query for each row, creating a dependent query. Is there a way to eliminate the dependent query?

UPDATE: It seems that Neels's solution works, thanks!

But I was wondering if there is a way to generalize the solution to multiple language fallbacks? First try to get French, if that's not present, try Russian, and if that's not present, show base translation (English, or any other, depending on the original creation language)?

UPDATE2: I've built the query I needed for the updated question using Neel's solution and DRapp's solution. It can be found here http://www.sqlfiddle.com/#!2/28ca8/18 but I'll also past the queries here, for completeness sake.

Revised Data:

CREATE TABLE articles (
  id INT,
  title VARCHAR(20),
  language VARCHAR(20),
  translation_set_id INT);

INSERT INTO articles values
  (1,'A','en',0),
  (2,'B','en',2),
  (3,'B_ru','ru',2),
  (4,'C','en',4),
  (5,'C_ru','ru',4),
  (6,'D','en',6),
  (7,'D_fr','fr',6),
  (8,'E_ru','ru', 0),
  (9,'F_fr','fr', 0),
  (10,'G_ru','ru', 10),
  (11,'G_fr','fr', 10),
  (12,'G_en','en', 10);

Original query with 2 correlated sub-queries:

SELECT id, title, language, translation_set_id
FROM articles a
WHERE
  a.translation_set_id = 0
  OR (a.language = 'fr')
  OR (a.language = 'ru' AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id
            AND ac.language = 'fr'))
  OR (a.id = a.translation_set_id AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id
            AND (ac.language = 'fr' OR ac.language = 'ru'))
     );

Revised query:

SELECT  a.*
FROM articles a
LEFT JOIN articles ac ON ac.translation_set_id = a.id
  AND ac.language = 'fr'
LEFT JOIN articles ac2 ON ac2.translation_set_id = a.id
  AND ac2.language = 'ru'
WHERE a.translation_set_id = 0
  OR a.language = 'fr'
  OR (a.language = 'ru' AND ac.id IS NULL)
  OR (a.id = a.translation_set_id AND ac2.id IS NULL AND ac.id IS NULL);

---

Check out this SQL Fiddle:

http://www.sqlfiddle.com/#!2/c05d0/15

You can use this simple query to achieve your result.

SELECT  a.*
FROM articles a
LEFT OUTER JOIN articles ac ON ac.translation_set_id = a.translation_set_id
AND ac.language = 'ru'
WHERE a.translation_set_id = 0
OR a.language = 'ru'
OR (a.id = a.translation_set_id AND ac.id IS NULL);

Mysql query with count subquery, inner join and group

I'm definitely a noob with SQL, I've been busting my head to write a complex query with the following table structure in Postgresql:

CREATE TABLE reports
(
  reportid character varying(20) NOT NULL,
  userid integer NOT NULL,
  reporttype character varying(40) NOT NULL,
)

CREATE TABLE users
(
  userid serial NOT NULL,
  username character varying(20) NOT NULL,
)

The objective of the query is to fetch the amount of report types per user and display it in one column. There are three different types of reports.

A simple query with group-by will solve the problem but display it in different rows:

select count(*) as Amount,
       u.username,
       r.reporttype
from reports r,
     users u
where r.userid=u.userid
group by u.username,r.reporttype
order by u.username

---

SELECT
  username,
  (
  SELECT
    COUNT(*)
  FROM reports
  WHERE users.userid = reports.userid && reports.reporttype = 'Type1'
  ) As Type1,
  (
  SELECT
    COUNT(*)
  FROM reports
  WHERE users.userid = reports.userid && reports.reporttype = 'Type2'
  ) As Type2,
  (
  SELECT
    COUNT(*)
  FROM reports
  WHERE users.userid = reports.userid && reports.reporttype = 'Type3'
  ) As Type3
FROM
  users
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      reports
    WHERE
       users.userid = reports.userid
  )

MySQL NOT IN with subquery does not work as expected

I'm creating a application that will generate lists for email marketing campaigns. I have tables for contacts, emails, and campaigns. A campaign has many emails and a contact has many emails. The email is related to a contact and a campaign. Basically a table for a MANY to MANY relationship except I have other fields in the table for the result of the email (clicked, opened, unsubscribed, etc). There are also other tables but this is where I'm having the trouble.

I'm trying to use NOT IN with a subquery to get a list of contacts who have not received an email since a certain date with other conditions. An example query is this:

SELECT *
FROM `contact` `t`
WHERE (unsubscribed='1')
  AND t.id NOT IN
   (SELECT distinct contact_id
    FROM email, campaign
    WHERE email.campaign_id = campaign.id
      AND campaign.date_sent >= '2012-07-12')
ORDER BY rand()
LIMIT 10000

This returns 0 result. However, if I run the first condition:

select id
from contact
where unsubscribed=1

I have 9075 rows. Then, if I separately run the subquery:

SELECT distinct contact_id
FROM email, campaign
WHERE email.campaign_id = campaign.id
  AND campaign.date_sent >= '2012-07-12'

I have 116612 rows. Out of each of those results, I end up with 826 values that are duplicates. From what I can understand, this means that 9075-826=8249 records ARE unsubscribed=1 AND NOT IN the second query. So, my first query should be returning 8249 results but it is returning 0. I must be structuring the query wrong or using the wrong operators but I can not for the life of me figure out how to get this right.

Can anyone help? So many thanks in advance as this has had me stumped for like 3 days! :)

---

This is because

SELECT 1 FROM DUAL WHERE 1 NOT IN (NULL, 2)

won't return anything, whereas

SELECT 1 FROM DUAL WHERE 1 NOT IN (2)

will.

Please review the behaviour of NOT IN and NULL in MYSQL.

For your concern you should get away with it using NOT EXISTS instead of NOT IN:

SELECT * FROM `contact` `t`
WHERE (unsubscribed='1')
AND NOT EXISTS (
    SELECT * FROM email, campaign
    WHERE
        email.campaign_id = campaign.id
    AND campaign.date_sent >= '2012-07-12'
    AND t.id = contact_id
)
ORDER BY rand()
LIMIT 10000

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();