CODEWITHSUNDEEP
mysqlsqljoin
Juan Gonzales
Juan Gonzales

Reputation: 1977

NOT IN a specific table using MySQL

I have two tables:

  1. One holds all posts from my members and

  2. another holds ids of the post, and the users id to hide the post on their page

How can I select all posts except for those that are not in the hide table?

Pseudo statement example...

"SELECT * FROM posts WHERE NOT IN (other table)"

This is what I have so far

SELECT * FROM `acmPosting` LEFT JOIN `hidePosts` ON acmPosting.id = hidePosts.postID WHERE `sender`='$userID' OR `recip`='$userID' OR `sender` IN ($friendsArray) OR `recip` IN ($teamsArray) AND hp.id IS NULL ORDER BY `timeSent` DESC LIMIT 10

Upvotes: 2

Views: 88

Answers (5)

Iswanto San
Iswanto San

Reputation: 18569

Assume the second table is postHides, and both of the tables have a postId column: 

SELECT * FROM posts WHERE postId NOT IN (SELECT postId FROM postHides)

Upvotes: 5

kufudo
kufudo

Reputation: 2833

"SELECT * FROM posts WHERE post_id NOT IN (select post_id from other_table)"

Upvotes: 2

Paul Bellora
Paul Bellora

Reputation: 55223

This is known as an anti-join and should be equivalent using NOT IN (though with a different query plan):

SELECT *
FROM posts p
LEFT JOIN hiddenPosts hp on hp.id = p.id
WHERE hp.id IS NULL
;

See this article for more information: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL

Upvotes: 2

Abhishek Oza
Abhishek Oza

Reputation: 3480

Suppose the other table is named hide_list and the foreign key of posts.id in it is fk_post_id. Then the select statement will be:

SELECT posts.* from posts,hide_list WHERE NOT posts.id=hide_list.fk_post_id;

A more optimized solution will be:

SELECT posts.* from posts,(SELECT fk_post_id from hide_list) hide_list WHERE NOT posts.id=hide_list.fk_post_id;

Upvotes: 2

Arnold Daniels
Arnold Daniels

Reputation: 16573

SELECT post.* FROM post LEFT JOIN foo ON post.id = foo.post_id WHERE foo.post_id IS NULL

Upvotes: 2

Related Questions