Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    3

    Question Unanswered: select query for records which do not appear in another linked table

    I have a successful inner join query to select records from one table which have a corresponding record in another table with the same unique id. "select table1.name, table1.ID, table2.address from table1 inner join table2 on table1.ID=table2.ID"

    How can I select records from table1 whose ID does NOT appear in table2?

    Any help much appreciated.

    JGS

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    which database do you use ?

    This would help

    Greetings

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Feb 2002
    Posts
    3

    database type

    used to postgesql but client now insists on MS SQL Server - alas alack!
    thanks Jgs

  4. #4
    Join Date
    Feb 2002
    Location
    Baltimore, MD
    Posts
    26

    Lightbulb Two Possibilities

    First using an outer join

    SELECT table1.name,
    table1.ID,
    table2.address
    FROM table1 LEFT JOIN table2
    ON table1.ID=table2.ID
    WHERE table2.address IS NULL

    Second using a subquery

    SELECT table1.name,
    table1.ID
    FROM table1
    WHERE NOT EXISTS ( SELECT 1
    FROM table2
    WHERE table1.id = table2.id )

    Hope this helps.
    Rob Wilkerson

  5. #5
    Join Date
    Feb 2002
    Posts
    3

    Many thanks

    Really apprecate the help, used the subquery method.

    Many thanks

    Jon

Posting Permissions

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