Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Korea
    Posts
    9

    Unanswered: how to tune query??

    I think this is very silly question
    but It is hard for me

    SELECT email, host FROM WAITING_AUTH WHERE email NOT IN
    (SELECT email FROM MEMBER)
    AND host NOT IN (SELECT host FROM MEMBER)

    thanks~ Have a nice weekend

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure both email and host columns have indexes defined in both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Posts
    33
    Quote Originally Posted by openmind
    I think this is very silly question
    but It is hard for me

    SELECT email, host FROM WAITING_AUTH WHERE email NOT IN
    (SELECT email FROM MEMBER)
    AND host NOT IN (SELECT host FROM MEMBER)

    thanks~ Have a nice weekend
    SELECT WAITING_AUTH.email, WAITING_AUTH.host
    FROM WAITING_AUTH
    LEFT JOIN MEMBER as M_mail ON WAITING_AUTH.email = M_mail.email
    LEFT JOIN MEMBER as M_host ON WAITING_AUTH.host = M_host.host
    WHERE M_mail.email IS NULL AND M_host.host IS NULL

  4. #4
    Join Date
    Feb 2004
    Posts
    78
    Quote Originally Posted by AmitGeorge
    SELECT WAITING_AUTH.email, WAITING_AUTH.host
    FROM WAITING_AUTH
    LEFT JOIN MEMBER as M_mail ON WAITING_AUTH.email = M_mail.email
    LEFT JOIN MEMBER as M_host ON WAITING_AUTH.host = M_host.host
    WHERE M_mail.email IS NULL AND M_host.host IS NULL
    You may also wish to try :

    Code:
    SELECT wa.email, wa.host 
    FROM WAITING_AUTH WA 
    WHERE NOT EXISTS (SELECT 1 FROM MEMBER  WHERE EMAIL = WA.EMAIL)
    AND NOT EXISTS (SELECT 1 FROM MEMBER WHERE HOST = WA.HOST)
    that would find it where NEITHER ONE exists. If you are looking if the PAIR exists or not in the same MEMBER record, you would use this code:

    Code:
    SELECT wa.email, wa.host 
    FROM WAITING_AUTH WA 
    WHERE
    NOT EXISTS (SELECT 1 FROM MEMBER WHERE EMAIL = WA.EMAIL AND HOST = WA.HOST)

    Eric

Posting Permissions

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