Left Outer Join with 'NOt IN' statement

Posted on 2007-07-24
Last Modified: 2007-07-24
Hi - I am tasked to create a merged database from a few highly disparate databases on multiple servers. I dumped some of the raw data from two databases into a MS SQL server (which contains the third set of data). What is wrong with the following query? It returns no records, but a quick look at the tables confirms it should return many.

Select WC.Col001 AS Email, MCD.Fname, MCD.Lname, MCD.Address, MCD.Address2, MCD.City, MCD.State, MCD.Zip, MCD.Country, MCD.DoNotSend, MCD.SCMClient, MCD.LastSubRate, MCD.LastTransactionDate
FROM PRO AS WC left outer join MarkClientDump as MCD
on WC.Col001 = MCD.Email
where WC.Col001 NOT IN (select Col018 from SSIA_DATADUMP);
Question by:mcorsi62
    LVL 7

    Expert Comment

    maybe you should put ltrim(rtrim(EMAIL)) around your join on the EMAIL as well as the WHERE statement incase there is empty spaces or something? I am assuming that Col018 and Col001 are EMAIL fields?
    LVL 142

    Accepted Solution

    the query looks fine, so you must be looking "too fast" over the data...
    I guess natloz's suggestion is a good start.
    you might start with a concreate example, add a where clause to the 2 queries that should return exactly those records that you think that should match/not match...

    Author Comment

    Angel was right. Some idiot had mixed all the clients from all products together into one products (SSIA)database. This occured about 4 years ago so the records were buried. Hence my NOT IN statement was eliminating all my records. Thanks. Sorry for the stupid question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Read about achieving the basic levels of HRIS security in the workplace.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    794 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

    15 Experts available now in Live!

    Get 1:1 Help Now