SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Records NOT IN Other Table (MySQL)

    I want to select records from 1 table that are not in another table. What is the MySQL syntax to do that. I tried:

    select * from table1 where tid not in (select tid from table2);

    Any help would be appreciated

    Mike

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Depending on the version of mySQL, you can't use a subquery.

    I THINK the OUTER JOIN Syntax will give you what you want. It should give you any records that do NOT meet the criteria.

    The syntax should be something like this:
    [code]
    SELECT field1, field2, field3
    FROM first_table
    OUTER JOIN second_table
    ON first_table.keyfield = second_table.foreign_keyfield
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, you can't do subselects in mysql

    the trick here is a left outer join, selecting the rows that don't match
    Code:
    select table1.*
      from table1 
    left outer
      join table2
        on table1.tid = table2.tid
     where table2.tid is null
    EDIT: what's up with the semi-colons in my code? i typed spaces, i swear...
    Last edited by r937; Sep 5, 2002 at 12:28.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    LEFT OUTER JOIN, not just OUTER JOIN. See, I learned something today....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, the OUTER keyword is optional

    you were definitely on the right track, but in order to get the results mike wanted, the same as a NOT EXISTS subquery, you have to test for nulls in the right table of the left outer join


  6. #6
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    I appreciate your help and I am glad that someone else was able to benefit from my question.

    Mike


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
  •