Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    17

    Unanswered: Are WHERE NOT IN queries possible?

    is WHERE NOT IN possible
    I am just moving accross to MySQL from Access.

    Something I regulalrly do in Access to create selection lists is to wirite a SELECT query that excludes records that are already in another related table. something along the lines:

    SELECT * FROM mytable WHERE id NOT IN (SELECT id FROM myothertable)

    (syntax may not be quite right, its been I while since I did it)

    Is anything similar possible with MySQL? and if so any clues to the syntax.

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100

    Question

    I have no knowedge of MySQL, but I would try the following.

    SELECT * FROM mytable a WHERE NOT EXISTS (SELECT * FROM myothertable b where a.id = b.id )


    I re-write NOT IN as NOT EXISTS in MS SQL because it is faster.

    Tim S

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql does not do subselects prior to release 4.1

    use an outer join instead, testing for unmatched rows

    SELECT * FROM mytable
    left outer join myothertable
    on mytable.id = myothertable.id
    where myothertable.id is null


    rudy

  4. #4
    Join Date
    Jan 2003
    Posts
    17
    thanks Rudy I will give it a try

  5. #5
    Join Date
    Jan 2003
    Posts
    7

    Smile Thank you!

    I've been in the same situation. Thanks for clearing that up!

Posting Permissions

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