CODEWITHSUNDEEP
mysqlsql
JimRomeFan
JimRomeFan

Reputation: 413

Query Results Not Accurate

I have the following query which provides me with accurate results:

SELECT t.id
    FROM titles t
ORDER BY t.id

My results are:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

My second query also provides me with accurate results:

SELECT t.id
FROM titles t 
    JOIN subscriptions s
        ON t.id = s.title
WHERE s.user=2

Results:

10
11
14

So what I am trying to do is receive all the results from the first query that don't show up in the second query, so I run this:

SELECT t.id
    FROM titles t
ORDER BY t.id NOT IN 
(
SELECT t.id
FROM titles t 
    JOIN subscriptions s
        ON t.id = s.title
WHERE s.user=2 
);

But my results end up as this:

14
11
10
13
12
9
8
7
6
5
4
3
2
1

What am I doing wrong here? Any why is the order reversed in my second query?

Upvotes: 0

Views: 57

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

NOT IN should be a part of WHERE condition, not ORDER BY statement:

SELECT
    t.id
FROM
    titles t
WHERE
    t.id NOT IN 
    (
        SELECT t.id
        FROM titles t 
            JOIN subscriptions s
               ON t.id = s.title
        WHERE s.user=2 
    )
ORDER BY
    t.id

Upvotes: 5

Related Questions