CODEWITHSUNDEEP
mysql
Foster
Foster

Reputation: 385

MySQL subquery unwanted result

so I have come into this subquery

SELECT movies.movieID, movies.user 
FROM movies
WHERE movies.user IN(Select movies.user 
                     FROM movies 
                     WHERE movies.movieID = '19') 
AND movies.movieID <> (SELECT movies.movieID 
                           FROM movies 
                           WHERE movies.user='5'
                           AND movies.movieID = '19') 
AND movies.movieID <> '19'

and it gives me this result

--------------------------
|  movieID    | user     |
--------------------------
|    20       |   4      |
--------------------------
|    21       |   4      |
--------------------------
|    22       |   5      |
--------------------------
|    23       |   4      |
--------------------------

How can I get rid of any result that is from user 5?

I think the problem is at the 

AND movies.movieID <> (SELECT movies.movieID 
                           FROM movies 
                           WHERE movies.user='5'
                           AND movies.movieID = '19')

But When I try to run the query alone 

SELECT movies.movieID 
FROM movies 
WHERE movies.user='5'
AND movies.movieID = '19'

it gives me only 1 result out of 2, which is only 19, the movieID 22 is not returned. Where the result of this query is correct. Why the 22 is returned when I run it in subquery?

Can anybody help me please? thanks

Upvotes: 0

Views: 24

Answers (2)

mmking
mmking

Reputation: 1577

If you don't want the records with user = 5 then just add

AND movies.user <> '5'

Your subquery

SELECT movies.movieID 
FROM movies 
WHERE movies.user='5'
AND movies.movieID = '19'

says, select the movieID where the movieID is 19 and the user is 5. This query will return 19.

So this

AND movies.movieID <> (SELECT movies.movieID 
                       FROM movies 
                       WHERE movies.user='5'
                       AND movies.movieID = '19')

says I don't want movieID = 19, which is why in your final query you will still get the record with movieID = 5.

Upvotes: 2

Erik Blessman
Erik Blessman

Reputation: 693

The clause where you are filtering out the records with user = '5' is only filtering out records where the user = '5' and movieID = '19'

AND movies.movieID <> (SELECT movies.movieID 
                           FROM movies 
                           WHERE movies.user='5'
                           AND movies.movieID = '19')

This means that it is not filtering out the record where user = '5' and movieID = '22'

Depending on what you want, you might try:

AND movies.movieID <> (SELECT movies.movieID 
                           FROM movies 
                           WHERE movies.user='5'
                           OR movies.movieID = '19')

or just add AND user != 5 on the main WHERE clause

SELECT movies.movieID, movies.user 
FROM movies
WHERE movies.user IN(Select movies.user 
                     FROM movies 
                     WHERE movies.movieID = '19') 
AND movies.movieID <> (SELECT movies.movieID 
                           FROM movies 
                           WHERE movies.user='5'
                           AND movies.movieID = '19') 
AND movies.movieID <> '19'
AND user <> '5'

Upvotes: 1

Related Questions