harkly

blockUser.blockID will never be NULL, if the user isn't blocked they won't be on the list.

Sorry I screwed up what I am trying to achieve in my last post, at this point I am so twisted and confused! Been working on this part of the query for 2 days now and am more confused then ever My goal:: Using $clientID='Name1', how do I display a list of all id's in the table "user" that are not in the table "blockUser" under Name1 table: user id Name1 Name2 Name3 Name4 table: blockUser id -- userID -- blockID 1 -- Name1 -- Name2 2 -- Name2 -- Name1 Out put needs to be:: Name1 Name3 Name4 I can achieve this with $result = mysql_query(" SELECT user.userID FROM user WHERE userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID') ") But not in my large search. So trying to use a LEFT JOIN how can I achieve it? This one displays all in 'user' and also the one being blocked (so it is there 2x) $result = mysql_query(" SELECT user.userID FROM user LEFT JOIN blockUser ON (user.userID = blockUser.blockUserID AND blockUser.blockID != user.userID) ") This one displays all in 'user' $result = mysql_query(" SELECT user.userID FROM user LEFT JOIN blockUser ON (user.userID = blockUser.blockUserID AND blockUser.blockID = user.userID) ") I know I am missing something, just can't figure it out. I think there needs to be a somewhere

So if I use a LEFT JOIN then how can I get it to work? LEFT JOIN blockUser ON user.userID = blockUser.blockUserID here - I need the blockUser.blockID != $clientID

I wasn't using a LEFT JOIN for the blockUser table because I couldn't get it to work. If I use a LEFT JOIN within the query do I have to use it for every table??

Means that it is not eliminating the data that is in the blockUser table. So when Name1 is logged in and does a search it is pulling Name2 up and it should not be. However in the simple query it doesn't pull Name2 up. So I am guessing that either I can't use the NOT IN with my log WHERE or I need to add something else to the SELECT statement.

Thanks! I got it to work in a simply query $result = mysql_query(" SELECT user.userID FROM user WHERE userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID') ") and now I need to incorporate it into a much larger one. I just added it to my WHERE statement, do I need to do more? Because its not working WHERE zip IN (". $zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= CURDATE() AND user.userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID') This is the full search $result = mysql_query("SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1, CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5)) AS age FROM user LEFT JOIN photos ON user.userID = photos.userID LEFT JOIN about_me ON user.userID = about_me.userID AND about_me.smoking = $smoke LEFT JOIN bkgd ON user.userID = bkgd.userID AND bkgd.relg = $religion LEFT JOIN appearance ON user.userID = appearance.userID AND appearance.height >= $heightMin AND appearance.height <= $heightMax AND appearance.eye_color = $eyeColor WHERE zip IN (". $zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= CURDATE() AND user.userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID') LIMIT $offset, $rowsperpage")or die(mysql_error()); $clientID is set

I am trying to run a query and get back all ids in a table that are not in another table. table: user id Name1 Name2 Name3 Name4 table: blockUser id -- userID -- blockID 1 -- Name1 -- Name2 2 -- Name2 -- Name1 My goal - When Name1 is logged in and does a search every name should show up except for Name2 because it is in the blockUser table. $result = mysql_query(" SELECT user.userID, blockUser.blockID, blockUser.userID FROM user inner JOIN blockUser ON (user.userID = blockUser.blockID) ") Can someone help me out??