Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Question Unanswered: Query Help - Join statement

    hi ,

    Pls help with a query where certain fields FROM table 1 is to be selected WHICH are not present in table 2.

    Hav tried the general query of "SELECT table1.item_id FROM table1 where table1.item_id NOT IN (SELECT item_id FROM table 2)" but it does not work in the mysql DB whereas the same query worked well with INgRES ...

    Pls help if it could work with a JOIN statement and how ...

    Thanks
    regards
    madonna

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madonnagrace
    but it does not work in the mysql DB
    oh yes it does

    well, it does assuming you are on a recent version, and by recent i mean one that was released in the current millenium

    anyone still running 3.23 or 4.0 deserves these types of difficulties

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The key is an OUTER join
    Code:
    SELECT table1.item_id
    FROM   table1
     LEFT
      JOIN table2
        ON table2.item_id = table1.item_id
    WHERE  table2.item_id IS NULL
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2009
    Posts
    4
    thanks gvee georgee ... and r937 will have a look at your SQl book for future ref if any ...

Posting Permissions

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