www.webdeveloper.com
Results 1 to 12 of 12

Thread: mysql "select not in"

  1. #1
    Join Date
    Dec 2003
    Posts
    249

    Question mysql "select not in"

    Hello

    The "select not in" statement doesn't seem to work in mysql... Is there an equivalent?
    I already came across the LEFT JOIN but I don't like such a solution.

    Is there an easier way?

    Ex.

    SELECT email FROM tblusers
    WHERE email NOT IN (
    SELECT email FROM tblclients);

    Thanks!

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    what do you mean it doesn't seem to work? do you have an example as it seems to work just fine in my testing. what version do you use?
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Dec 2003
    Posts
    249
    Hi again, sorry for the late reply.
    This is what I can't get working:

    SELECT id, email_default
    FROM lnk_partners
    WHERE id >= '0'
    AND id < '500'
    AND statusid = '2'
    AND email_default NOT
    IN (

    SELECT email
    FROM tbl_participants
    )
    ORDER BY id ASC

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax near 'SELECT email FROM tbl_participants ) ORDER BY id ASC LIMIT 0, 30' at line 1

    Any help?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    it's because of that extra line you got there. get read of the blank line between ( and the SELECT and it should be fine.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  5. #5
    Join Date
    Dec 2003
    Posts
    249
    hmm, no it's not that. The snippet there is a copy/paste of what phpmyadmin makes of it. I submit the sql in 1 line.

  6. #6
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    ok, then again i ask you "what version do you use?"
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  7. #7
    Join Date
    Dec 2003
    Posts
    249
    MYSQL versie 3.23.58

  8. #8
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    well that's why.

    you're using too old of a version. subqueries aren't supported until 4.1
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  9. #9
    Join Date
    Dec 2003
    Posts
    249
    hmm damn.
    Anyone has the lefty join solution?

    I'll try on the first query:

    SELECT u.email FROM tblusers u
    LEFT JOIN tblclients c on c.email = u.email
    ;

    That correct?

  10. #10
    Join Date
    May 2006
    Posts
    11
    MySQL is free, so updating is free to. Seems to be a better solution then rebuilding the query.

  11. #11
    Join Date
    Dec 2003
    Posts
    249
    Aye, but I am not my own webhost...

  12. #12
    Join Date
    May 2006
    Posts
    11
    Hmmz that's a small detail :-)

    Then you don't have much choice...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles