Friends blocking list

DavyBoy · #1 (**permalink**) 08-12-10, 07:32

Hi,

I was reading DarkZlayers post about storing friends and my problem is kind of the opposite.

I have two tables, one standard users table with each user having a unique numerical ID and the other of blocked users, the first column being the ID of the user and the second being the ID of the user who has been blocked by that user.

So, let's say Dave wants to block Mary: the first column would be Dave's ID and the second would be Mary's ID. Dave should never see Mary in his list again, but Mary should be available for other others (unless they block her). At the same time, Mary should never see Dave again even though she's not performed any kind of blocking action.

I'm having real problems with the query to select users who are not blocked. I know I need to do two additional joins within the blocked users table but nothing I'm doing seems to work.

So for example: Dave's user id is 100 and he wants to view all users who aren't himself as well as those he hasn't blocked and those who haven't blocked him.

The query below doesn't work, but should give a list of all users Dave has blocked and who have blocked him:

select users.userid, firstname
from users
left join blocked_users a on (users.userid=a.blockeduserid and a.userid=100)
left join blocked_users b on (users.userid=b.userid and a.blockeduserid=100)

However, I want to do the *opposite* of this and I can't work out how. Although I'd also like to make the above query work too, though that's less important.

Any ideas?

r937 · #2 (**permalink**) 08-12-10, 07:43

try this --

Code:

SELECT users.userid
     , users.firstname 
  FROM users
LEFT OUTER
  JOIN blocked_users a 
    ON a.userid = 100 
   AND a.blockeduserid = users.userid
LEFT OUTER
  JOIN blocked_users b 
    ON b.blockeduserid = 100
   AND b.userid = users.userid
 WHERE a.blockeduserid IS NULL
   AND b.blockeduserid IS NULL

alternatively, try this --

Code:

SELECT userid
     , firstname 
  FROM users
 WHERE NOT EXISTS
       ( SELECT 'uhoh'
           FROM blocked_users 
          WHERE blocked_users.userid = 100
            AND blocked_users.blockeduserid = users.userid 
             OR blocked_users.blockeduserid = 100
            AND blocked_users.userid = users.userid )

DavyBoy · #3 (**permalink**) 08-12-10, 20:59

Sorry for my delay in responding, I didn't think anyone would get back to me so quickly! (I'm a first time poster)

I haven't tried the second solution yet, because the first one worked like a dream. I even noticed that I forgot to mention blocked users have a certain status and are not deleted if/when you unblock them, but your query allowed me to include that.

Thank you very much... I get to keep some hair now ;-)

Internet Services

Web Development

Digital Marketing

Consumer Tech