SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    any other way to do this "WHERE IN" + "WHERE NOT IN"

    hi all

    i have this code
    where i'm trying to check if my msn contacts
    are also contacts on my web site

    i keep email addresses and msn addresses in the database
    but obviously not everyone uses the same address as what they register with

    so after they log into msn, i get all their msn addresses and then compare them to what is in our database
    but this query seems to take a while
    and since i have not had great experience with "WHERE (NOT) IN"
    i wanted to know if there is anything else i can use that may be faster

    the addresses in the "WHERE IN" part of taken from the msn login details
    and so i don't know how else i can check them
    and user_ID's are taken from another separate query from another table that stores contacts details
    i guess i could probably try to integrate that into a subquery, but not sure if there are any performance gains to be had there

    Code:
      SELECT user_Nick, user_ID, user_Email, user_MSN
     FROM urban_userlog
     WHERE (user_Email
     IN ( 'sample@domain.com', 'test@hotmail.com')
     OR user_MSN
     IN ( 'sample@domain.com', 'test@hotmail.com')
     )
     AND user_ID NOT 
     IN ( '1', '2')
     AND user_AccountStatus =  'a'
     GROUP  BY user_Nick
    thanks
    dave

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    subqueries will always be faster than running one query and editing the results into a query string to run a second query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, thanks
    but nothing i can do with the where in clauses?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh, there probably is

    any time you use one query to create what's necessary to run a second query, you can probably do it all in a single query with a join

    i'd need to see all your queries (and as little php as possible, because i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     SELECT user_Nick, user_ID, user_Email, user_MSN FROM urban_userlog 
     	WHERE (user_Email IN (email@sample.com, sample@email.com) OR user_MSN IN (email@sample.com, sample@email.com)) 
     	AND user_ID NOT IN (SELECT user_ID FROM urban_userlog 
     		INNER JOIN urban_msndirectorycheck2 ON user_ID = msndirectorycheck_canLookAtUserID 
     		WHERE msndirectorycheck_userID = '$userID') 
     	AND user_ID <> '$userID' 
     	AND user_AccountStatus = 'a' 
     	GROUP BY user_Nick
    basically the emails i don't think can be changed much, since they are not taken from the database
    rather from a list generated from the php
    but maybe the second userID part?

    thanks
    dave

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you might be able to do away with the join in the subquery, and simply check WHERE user_id NOT IN (SELECT msndirectorycheck_canLookAtUserID ...)

    otherwise the query looks okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh ok
    i'll have a look at that
    thanks


Bookmarks

Posting Permissions

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