Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: Friends blocking list

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    Last edited by r937; 08-12-10 at 08:47.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    3

    Brilliant!

    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 ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •