SQL Join can't figure it out

Question

I have a table called website that contains some data about websites. The columns of this table are: id, website, quick_url, user_id, status, etc.

Each website that is in the table was added by a user, which is is saved in the user_id column.

I have another table called blocks that has only 3 columns: id, user_id, website_id.

I want to get all the websites from the website table, that were not added by a given user_id, but also, only the websites that were not blocked by the given user_id. So, websites that were not added by a given user or blocked by him.

Here is what I've tried:

SELECT * FROM website LEFT OUTER JOIN blocks ON tbl_website.userid = blocks.user_id WHERE website.user_id = blocks.user_id AND blocks.user_id = NULL AND website,user_id != '177' LIMIT 500;

It doesn't give me the wanted results ...

First, I've tried to do it like this:

SELECT * FROM tbl_website WHERE id<>(SELECT website_id from tbl_website_blocks WHERE user_id = '177')

which makes much more sense for me than my previous query, but I get this error: Subquery returns more than 1 row

I guess you can't have a "loop in loop" in an SQL query.

I'm aware that I could do two queries, and filter the results, but I would like to do it as much as possible from the SQL language, so that I don't "overload" the server.

Any suggestions would be appreciated.

Answer 1

Instead of '<>', try 'Not In'

SELECT * FROM tbl_website 
WHERE id Not In (SELECT website_id from tbl_website_blocks WHERE user_id = '177')

I should also add this query is not a Join.

Answer 2

In your second query rewrite the condition on

WHERE id not in (SELECT website_id from.....) 

with <> you can compare it with just one value but your select returns list of values, so you can use not in to get results that are different then the selected list of IDs