/* this would get the user */
SELECT *
FROM users
WHERE id = $ID
/* build on this to get relationships */
SELECT *
FROM users u
JOIN relationship r ON r.user_id = u.id
WHERE u.id = $ID
/* build on this to get not blocked */
SELECT *
FROM users u
JOIN relationship r ON r.user_id = u.id
JOIN relationship_block b ON b.user_id = u.id
WHERE u.id = $ID
AND r.member_ID <> b.blocked_member_id
/* get all users that NO ONE has blocked */
/* this means if there exists a record b such that b.blocked_member_id
equals the user X has blocked user Y, do not include user Y.
By extension, if X and Y are fierce enemies and have blocked eachother,
neither would get returned by the query */
SELECT *
FROM users u
JOIN relationship r ON r.id = u.id
WHERE NOT EXISTS ( SELECT null
FROM relationship_block rb
WHERE rb.blocked_member_id = u.id
)
/* This runs two queries at once. The inner query says "I'm not getting any
columns, because I don't care about the actual data, I just to get all
records where someone has blocked the user I'm currently looking for".
Then you select all users where that isn't true. For good speed, this would
require an index on relationship_block.blocked_member_id */

It occurs to me that this can be interpreted to mean you need three queries to get this done. You don't. The first two were merely showing the thought progression of how I would build the query required.
–
corsiKaFeb 17 '11 at 20:55

I may be doing something wrong, but this doesn't seem to work. It may be that in relationship_blocks table is only one or 2 rows (blocks) but there is many more user rows.
–
DavidHavlFeb 18 '11 at 11:04

In even more easier explanation I need to return all members from relationship table (all member_ids) that are not present in relationship_blocks table . Therefore for example if there is no row in relationship_blocks table it would return all the users. If there is 1 row in relationship_blocks table that has : 'user_id' = 1 , 'blocked_member_id' = 4 then it would return all users (presented in relationship table as member_id) except for user with id 4
–
DavidHavlFeb 18 '11 at 11:05