Results 1 to 6 of 6

Thread: How query for Records in table 1, but not in table 2 ?

Hybrid View

  1. #1
    Join Date
    Aug 2003
    Posts
    3

    How query for Records in table 1, but not in table 2 ?

    I am a very much novice at Access. Please help.

    Have two tables with data about servers from two sources.

    Table1 has HOSTNAME, SERIAL, IP

    Table2 has HOSTNAME, SERIAL, IP

    I need
    Hostnames that appear on Table1, but not on Table2.

    I put the fields of Table1 in a query. In criteria in first column under HOSTNAME, I put
    <>All (SELECT [HOSTNAME] FROM [Table2] WHERE [HOSTNAME] = HOSTNAME)

    But get nothing. I think because I am joining on this field and/or because I am selecting for hostnames the same where I am looking for hostnames different, but when I take the where clause out, I get syntax error.

  2. #2
    Join Date
    Aug 2003
    Location
    In a galaxy far, far away...
    Posts
    28
    Try this

    SELECT [Table 1].hostname, [Table 1].ip
    FROM [Table 1] LEFT JOIN [Table 2] ON [Table 1].hostname = [Table 2].hostname
    WHERE ((([Table 2].hostname) Is Null));

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    On right track but need NOT found in table2

    Thanks Corrupter for your reply. We are close. I removed the Where because I don't want only when NULL in table2.

    What I end up with is all in table1 that ARE in table2. Guess I want the converse - those in table1 that do not appear in table2.

    Would I just use NOT or !=? Will try some of that stuff.

  4. #4
    Join Date
    Aug 2003
    Location
    In a galaxy far, far away...
    Posts
    28
    I do not understand you.

    If you want to select all those records are in table1 but not in table2 use the query I told you. That query is a simple unmatch query.

    If you want to select thosre are in both tables simply modify the where clause like WHERE ((([Table 2].hostname) = [Table 1].hostname)))


    The where clause you have to use otherwise the server won't be able to narrow your selection.


    To learn more about queries try to use query wizard in Access (Select queries and clik on "New").

  5. #5
    Join Date
    Aug 2003
    Location
    In a galaxy far, far away...
    Posts
    28
    Sorry but I did not answer your question.

    Since in the query we joined the two tables with identical columns when the record pointer will be moved in table1 to a record doesn't appear in table2 the record pointter will point toa NULL in table2 since the record (or any records matching the criteria we defined) does not exist in table2 therefore isn'T it logical to use Is Null statement in where clause?

    Of course you can use <> or != but doesn't make any sense.

  6. #6
    Join Date
    Aug 2003
    Posts
    3

    NOT IN (select hostname from table2...)

    Corruptor

    Was able to tweak it up to get needed result using NOT IN select from (table2) kind of thing. The IN seems to make the pointer advance through the select subquery comparing each result to the main query condition.

    Thanks for your help. I probably confused you with my wording. But you have brought about the solution to the problem.

    Thank you...

Posting Permissions

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