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
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.
JOIN blocked_users a
ON a.userid = 100
AND a.blockeduserid = users.userid
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 --
WHERE NOT EXISTS
( SELECT 'uhoh'
WHERE blocked_users.userid = 100
AND blocked_users.blockeduserid = users.userid
OR blocked_users.blockeduserid = 100
AND blocked_users.userid = users.userid )
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 ;-)