excluding data from sql output if..

[Jaynesh]

Hello

 

I have two tables

 

post(post_id, user_id, post)

ignore(post_id, user_id)

 

I have set it up so it outputs all the contents within the 'post' column.

How would I stop it from outputting all the posts that are in the ignore table?

 

 

[joel24]

SELECT * FROM post WHERE post_id NOT IN (SELECT post_id FROM ignore)

though if user_id in the ignore table is the user who ignored the post, you would have something like this... assuming the user id is stored in the session

SELECT * FROM post WHERE post_id NOT IN (SELECT post_id FROM ignore WHERE user_id='{$_SESSION['user_id']}')

[Jaynesh]

Hi.

 

It doesn't seem to be working.

 

My code is a little more complicated, I'm not sure I'm implementing it correctly.

I've created a friends system, whenever a friend creates a post it will be displayed to his/her friends.

This is the query that displays it to them. I want to filter out any posts that are ignored.

 

SELECT Users.id, Posts.post, Users.username, Posts.post_id
FROM Posts, Users, Friends
WHERE Posts.username_id = Users.id 
AND Friends.user_id = $user 
AND Friends.friend_id = Posts.username_id
NOT IN (SELECT Posts.post_id FROM ignore)

[joel24]

you have to select post_id from ignore, not posts.post_id, as they are seperate queries...

SELECT Users.id, Posts.post, Users.username, Posts.post_id
FROM Posts, Users, Friends
WHERE Posts.username_id = Users.id 
AND Friends.user_id = $user 
AND Friends.friend_id = Posts.username_id
NOT IN (SELECT post_id FROM ignore)

 

.. though that will return the post_id of every ignored post, by any user. you need to change the last select to incorporate the user's id, i.e.

SELECT Users.id, Posts.post, Users.username, Posts.post_id
FROM Posts, Users, Friends
WHERE Posts.username_id = Users.id 
AND Friends.user_id = $user 
AND Friends.friend_id = Posts.username_id
NOT IN (SELECT post_id FROM ignore WHERE user_id=$user)

[Jaynesh]

Thank you!!