Need help with a complex SQL statement

Hi Im trying  to  start up a dating site (in php)  but need some help with writing an SQL statement for showing members that have signed up.

Basically members are shown via an online form but I need to filter out those that have blocked the person that happens to be viewing.  

For example if member gorgeous_girl blocks member dirty_old_man&&so when dirty_old_man logs in and browses he can see all members except for gorgeous_girl since shes blocked him.

So these are my mySQL tables

blocked

id
id_of_banned,
name_of_banned,
ip_of_banned,
banned_by varchar,
id_of_banned_by


members

id,
name,
age,
username,
password,
email,
date_joined,
self,
seeking,
location,
logged_in,
ip












now heres my statement before any blocking of the members&.and it works fine.

SELECT id,
username,
intro,
photo
 FROM members WHERE (self = '$_SESSION[seeking]' AND seeking = '$_SESSION[self]')
 AND
 (age BETWEEN '$_SESSION[from]' AND '$_SESSION[to]') ORDER BY logged_in DESC

Now then I try to filter out those that blocked the member thats logged in currently using a NOT IN sub query like so&.

SELECT id,
username,
intro,
photo
 FROM members WHERE (self = '$_SESSION[seeking]' AND seeking = '$_SESSION[self]')
 AND
(age BETWEEN '$_SESSION[from]' AND '$_SESSION[to]')
AND
 (username NOT IN (SELECT banned_by FROM blocked JOIN members ON members.name = blocked.name_of_banned WHERE blocked.name_of_banned = '$_SESSION[username]')) ORDER BY logged_in DESC

Now the query does run without errors but it doesnt do the job of not showing the people that blocked the current viewer.  

So now here is a simpler version that does work!

SELECT username
FROM members
WHERE username NOT
IN (

SELECT banned_by
FROM blocked
JOIN members ON members.username = blocked.name_of_banned
WHERE blocked.name_of_banned = '$_SESSION[username]' OR blocked.ip_of_banned = '$_SERVER[REMOTE_ADDR]'
)



So basically can someone please advise on how the more complex version should be&..I think where Im going wrong is in the order of all the stuff after the where clause and before the subquery.

many thanks



Juan.
Start Free Trial