Join To Find Exclusion

  • I have two large tables, CustomersA and CustomersB. Columns in both tables are equivalent.

    I need to find all rows from CustomersA where Company and ZipCode (5 digit) are NOT IN CustomersB.

    What kind of JOIN or other syntax could I use to find these? I am having a forgetful day!

    Thank you!

  • select a.* from customera a

    join customerb b on (a.company+a.zipcode<>b.company+b.zipcode)



    Nuke the site from orbit, its the only way to be sure... :w00t:

  • Thank you very much! I have used that plenty of times but just did not think to use it in this case.

  • select a.* from customera a

    join customerb b on (a.company+a.zipcode<>b.company+b.zipcode)

    Ummmm... You might wanna go back and take a look at that code...

    [font="Courier New"]--=====&nbspSetup&nbspa&nbsptest&nbsptable&nbspand&nbsppopulate&nbspwith&nbsptest&nbspdata

    &nbspCREATE&nbspTABLE&nbspCustomersA&nbsp(Company&nbspVARCHAR(5),&nbspZipCode&nbspCHAR(5))

    &nbspINSERT&nbspINTO&nbspCustomersA&nbsp

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(Company,ZipCode)

    &nbspSELECT&nbsp'AAA',&nbsp'11111'&nbspUNION&nbspALL

    &nbspSELECT&nbsp'BBB',&nbsp'22222'&nbspUNION&nbspALL

    &nbspSELECT&nbsp'CCC',&nbsp'33333'&nbspUNION&nbspALL

    &nbspSELECT&nbsp'DDD',&nbsp'44444'&nbspUNION&nbspALL

    &nbspSELECT&nbsp'EEE',&nbsp'55555'

    --=====&nbspSetup&nbspidentical&nbsptest&nbsptable&nbspand&nbsppopulate&nbspwith&nbspidentical&nbspdata

    &nbsp&nbsp&nbsp&nbsp&nbsp--&nbspexcept&nbspfor&nbspone&nbsprow

    &nbspCREATE&nbspTABLE&nbspCustomersB&nbsp(Company&nbspVARCHAR(5),&nbspZipCode&nbspCHAR(5))

    &nbspINSERT&nbspINTO&nbspCustomersB

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(Company,ZipCode)

    &nbspSELECT&nbspCompany,ZipCode

    &nbsp&nbsp&nbspFROM&nbspTableA

    &nbsp&nbspWHERE&nbspCompany&nbsp<>&nbsp'CCC'

    --=====&nbspPostIt's&nbspcode

    select&nbspa.*&nbspfrom&nbspcustomersa&nbspa

    join&nbspcustomersb&nbspb&nbspon&nbsp(a.company+a.zipcode<>b.company+b.zipcode)[/font]

    Try this, instead...

    [font="Courier New"]SELECT&nbspa.*

    &nbsp&nbsp&nbspFROM&nbspCustomersA&nbspa

    &nbsp&nbsp&nbspLEFT&nbspOUTER&nbspJOIN&nbspCustomersB&nbspb

    &nbsp&nbsp&nbsp&nbsp&nbspON&nbspa.Company&nbsp=&nbspb.Company

    &nbsp&nbsp&nbsp&nbspAND&nbspa.ZipCode&nbsp=&nbspb.ZipCode

    &nbsp&nbspWHERE&nbspb.Company&nbspIS&nbspNULL[/font]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply