How can I exclude a certain row from an MySQL query

Question

I want to exclude a certain row from an MYSQL query.

SELECT * FROM imdb WHERE genres LIKE 'Adventure' ORDER BY id DESC LIMIT 10

It's for a movie website, so I retrive similar Adventures movie, but this includes the current movie.

So I need something like this:

Select the movies like Adventures, but exclude this id: 1, yet, results 10 movies.

score 9 · Accepted Answer · edited May 23 '17 at 12:16

9

If you know the exact id (say, id = 1 for example), do this:

SELECT * FROM imdb WHERE genres LIKE 'Adventure' AND id <> 1 ORDER BY id DESC LIMIT 10

See this SO post.

edited May 23 '17 at 12:16

Community

1
1

answered Sep 02 '11 at 15:54

JW8

1,496
5
21
36

Thank you, it did work, and actually I did this before, but visually in the MySQL results show me the id I wanted to exclude too, but the text was Showing rows 0 - 3 (4 total, Query took 0.0012 sec) [id: 243 - 229], so thank you again for brining the light. – Special K. Sep 02 '11 at 16:04
@Wtstatscom: By the way, you can click the empty "Check Mark" symbol next to JW's answer to indicate that it worked for you =) – Josh Darnell Sep 02 '11 at 16:19
@jadarnel27 - Thank you & done, thank you guys. You were so fast with the replies. – Special K. Sep 06 '11 at 20:35

score 5 · Answer 2 · answered Sep 02 '11 at 15:55

Something like this maybe?

SELECT * 
FROM imdb 
WHERE genres LIKE 'Adventure' AND
  ID NOT IN (1)
ORDER BY id DESC LIMIT 10

You can put a list of IDs you don't want to include between the parenthesis after NOT IN.

EDIT: You could also put a query in between those parens if you know a particular group of ID's you want to exclude:

WHERE genres LIKE 'Adventure' AND
  ID NOT IN (SELECT ID FROM imdb WHERE LeadActor='Pauly Shore') --You know you want to exclude him =)

score 3 · Answer 3 · answered Sep 02 '11 at 15:57

3

SELECT * FROM imdb WHERE genres LIKE 'Adventure' and id != 1 ORDER BY id DESC LIMIT 10

answered Sep 02 '11 at 15:57

Roel Van Nyen

1,279
1
9
19

score 0 · Answer 4 · edited May 23 '17 at 14:20

0

For not showing current user data, below is the query

$sql = "SELECT u1.parent_id, m1.meta_value AS headline
        FROM wp_vandor u1
        JOIN wp_usermeta m1 ON (m1.user_id = u1.child_id AND m1.meta_key = 'headline') 
        WHERE m1.user_id!=$currentuser_id";

edited May 23 '17 at 14:20

amarnath

785
3
19
23

answered May 23 '17 at 12:21

Alpesh Navadiya

23
5

How can I exclude a certain row from an MySQL query

4 Answers4