How do I compare two fields to list all records that do not match?

Posted on 2014-08-13
Last Modified: 2014-08-19
I'm trying to compare field1 in table1 which has 4600 records with field2 in table2 which has 9500 records to list all records in table1 that are not in table2.
Question by:shwelopo
    LVL 4

    Expert Comment

    Hey.. you have to import the data first and then only can compare. without importing the data, you can't compare.

    Author Comment

    this is my query, it doesn't seem to work right.

    select distinct SUBSTRING(table1.field1, 1, 6) from table1

    not in (select SUBSTRING(table2.field2, 1, 6) from table2)
    LVL 3

    Expert Comment

    by:Sean Fitzpatrick
    there's a command to compare data from two tables, but you will have to import all the data into a single spreadsheet first as akalyan911 stated.

    Here's some examples of what you can do with tables.
    LVL 31

    Accepted Solution

    What dbms are you using? With the use of the substring function, perhaps it's sql server. If so you can use the except set operator -
    select substring(field1, 1, 6) from table1
    select substring(field2, 1, 6) from table2

    In ms access, there is a query wizard to find unmatched records you can use.

    In MySQL, you can use something like the following:
    select substring(field1, 1, 6) from table1 as t1
    where not exists
    (select 1 from table2 as t2
     where substring(t1.field1,1,6) = substring(t2.field2,1,6))

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now