Sql join 3 tables

Question

I have struggle to produce good SQL. It is a relationship part of application. I have 3 tables : users('id'), relationship('user_id', 'member_id'), relationship_block('user_id', 'blocked_member_id')

I would like to get all members belonging to user with user_id that are not blocked.

Records of first table 'users' that are in 'relationships' table but that are NOT in 'relationship_blocked' table. First 2 i can do with JOIN, but then I want to remove those that are blocked.

Thanx.

edit: found a good info about it here: http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/

Answer 1

/* 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 */

Answer 2

select *
    from users u
        inner join relationship r
            on u.user_id = r.user_id
        left join relationship_block rb
            on r.user_id = rb.user_id
                and r.member_id = rb.blocked_member_id
    where rb.user_id is null