SELECT * FROM users WHERE Users are not in Blocked

[brown2005]

How do I select all users from users table where there users_id is not stored in the block table

[gevans]

SELECT * FROM `users`
WHERE `id` <> IN (SELECT `usersId` FROM `block`)

 

Something along the lines of that will do it for you

[brown2005]

SELECT * FROM users WHERE user_id <> IN (SELECT blocked_blocked FROM block) ORDER BY user_name ASC

 

i have tried that but does not work

[gevans]

Try it with the NOT operator;

 

SELECT * FROM users WHERE user_id NOT IN (SELECT blocked_blocked FROM block) ORDER BY user_name ASC

[brown2005]

SQL query:  

SELECT * 
FROM users
WHERE user_id NOT 
IN (


SELECT blocked_blocked
FROM blocked
)
ORDER BY user_name AS 
LIMIT 0 , 30 

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS 
LIMIT 0, 30' at line 1 

[gevans]

You have AS instead of ASC

[brown2005]

oh yes. damn sorry. thanks for the help. works fine. also if in the future i wanted to add say a filter as will, like display

 

blocked_user from blocked where = $_SESSION['session_id'];

 

how would I change the code.

[brown2005]

SELECT * FROM users WHERE user_id NOT IN (SELECT blocked_blocked FROM blocked WHERE blocked_user=$session_id) ORDER BY user_name ASC

 

I have worked out like that. I presume it is correct as it works.

[gevans]

Yes that's fine. I would adda  condition to make sure you don't attempt to do that when the filtering is not wanted though!