query

[harkly]

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??

[requinix]

You can do a LEFT JOIN and only pick the rows where the blockUser id is NULL, or you can do a NOT IN with the user's id and the blockUser table.

[harkly]

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

[requinix]

What does "not working" mean?

[harkly]

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.

 

 

[fenway]

If you use LEFT JOIN, then any conditions on the LEFT JOIN-ed table fields needs to be the ON clause, not the where clause.

[harkly]

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??

[fenway]

If the subsequent tables refer to a LEFT JOIN'ed table, then yes.

[harkly]

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

 

 

[fenway]

LEFT JOIN blockUser

ON ( user.userID = blockUser.blockUserID AND blockUser.blockID != $clientID )

[harkly]

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

blockUser.blockID != user.userID

blockUser.blockUserID ='$clientID'

somewhere

[fenway]

You're missing WHERE blockUser.blockID  IS NULL.

[harkly]

You're missing WHERE blockUser.blockID  IS NULL.

 

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

[fenway]

You're missing WHERE blockUser.blockID  IS NULL.

 

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

Then you don't understand what a LEFT JOIN does.

[harkly]

How else can I achieve the results that I am looking for?

 

I was trying to use NOT IN but could not get that to work in the large search

 

WHERE userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID')

[harkly]

Got it to work (NOT IN)and so pissed at myself!!! Days on this issue and it turns out that I was not putting the code in both my if and else so it didn't appear to be working!!!

 

Thanks for all the help!