Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Left Outer Join with 'NOt IN' statement

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);
1 Solution
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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
mcorsi62Author Commented:
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.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now