Get data that did not fill the query

存储架构 2017-11-05

I have two tables that has information about some contacts, I'm trying to build some queries to match these contacts together if they exist under the following criteria:

  1. Matching FirstName,LastName and Email
  2. Matching FirstName and Email
  3. Matching LastName and Email

I built a query to get the first criteria which is pretty straight forward, now for the second criteria I want match based on the data that was left from the first criteria(I don't want the second query to contain data that is already in the first one)

I wrote this query to get the data that was not in the first query but it broke the application (I'm using access DB as a business requirement)

I know this query is wrong but can you please help me or guide me on how to solve the second query.

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA, TableB
WHERE TableB.[UIR No] NOT IN (SELECT TableB.[UIR No] FROM TableA, TableB WHERE (((TableA.[Last Name])=TableB.[Last Name]) And ((TableA.Email)=TableB.Email) And ((TableA.[First Name])=TableB.[First Name]));)
AND
TableA.[Contact ID] NOT IN(SELECT  TableA.[Contact ID] FROM TableA, TableB WHERE (((TableA.[Last Name])=TableB.[Last Name]) And ((TableA.Email)=TableB.Email) And ((TableA.[First Name])=TableB.[First Name])););

In cases like this I tend to prefer breaking down the queries into individual "base queries" and saving them. Then I create "result queries" based on the "base queries". In this case I would start by creating the "base queries" for the three ways of matching:

[qry_base_1_matchFirstLastEmail]:

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA INNER JOIN TableB ON (TableA.Email = TableB.Email) AND (TableA.[Last Name] = TableB.[Last Name]) AND (TableA.[First Name] = TableB.[First Name]);

[qry_base_2_matchFirstEmail]:

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA INNER JOIN TableB ON (TableA.Email = TableB.Email) AND (TableA.[First Name] = TableB.[First Name]);

[qry_base_3_matchLastEmail]:

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA INNER JOIN TableB ON (TableA.[Last Name] = TableB.[Last Name]) AND (TableA.Email = TableB.Email);

Then I can use those as the source for the "result queries" where step (2) omits the results from step (1), and step (3) omits the results from both (2) and (1):

Step 1: Match on [First Name], [Last Name] and [Email]

We already have that as one of our "base queries" so we have to choose between simply renaming qry_base_1_matchFirstLastEmail
to qry_result_1_matchOnlyFirstLastEmail
(which might be confusing), or creating a new qry_result_1_matchOnlyFirstLastEmail
that simply returns the results from qry_base_1_matchFirstLastEmail
(which is a bit inefficient). I'm easily confused, so I'll just go with the second option

[qry_result_1_matchOnlyFirstLastEmail]:

SELECT qry_base_1_matchFirstLastEmail.*
FROM qry_base_1_matchFirstLastEmail;

Step 2: Match on just
[First Name] and [Email]

We want to take the results from qry_base_2_matchFirstEmail
and eliminate the ones that we already identified in step 1:

[qry_result_2_matchOnlyFirstEmail]:

SELECT qry_base_2_matchFirstEmail.[Contact ID], qry_base_2_matchFirstEmail.[UIR No]
FROM qry_base_2_matchFirstEmail LEFT JOIN qry_base_1_matchFirstLastEmail ON (qry_base_2_matchFirstEmail.[UIR No] = qry_base_1_matchFirstLastEmail.[UIR No]) AND (qry_base_2_matchFirstEmail.[Contact ID] = qry_base_1_matchFirstLastEmail.[Contact ID])
WHERE (((qry_base_1_matchFirstLastEmail.[Contact ID]) Is Null) AND ((qry_base_1_matchFirstLastEmail.[UIR No]) Is Null));

Step 3: Match on just
[Last Name] and [Email]

We want to take the results from qry_base_3_matchLastEmail
and eliminate the ones that we already identified in steps 1 and 2:

[qry_result_3_matchOnlyLastEmail]:

SELECT qry_base_3_matchLastEmail.[Contact ID], qry_base_3_matchLastEmail.[UIR No]
FROM (qry_base_3_matchLastEmail LEFT JOIN qry_base_2_matchFirstEmail ON (qry_base_3_matchLastEmail.[UIR No] = qry_base_2_matchFirstEmail.[UIR No]) AND (qry_base_3_matchLastEmail.[Contact ID] = qry_base_2_matchFirstEmail.[Contact ID])) LEFT JOIN qry_base_1_matchFirstLastEmail ON (qry_base_3_matchLastEmail.[UIR No] = qry_base_1_matchFirstLastEmail.[UIR No]) AND (qry_base_3_matchLastEmail.[Contact ID] = qry_base_1_matchFirstLastEmail.[Contact ID])
WHERE (((qry_base_2_matchFirstEmail.[Contact ID]) Is Null) AND ((qry_base_2_matchFirstEmail.[UIR No]) Is Null) AND ((qry_base_1_matchFirstLastEmail.[Contact ID]) Is Null) AND ((qry_base_1_matchFirstLastEmail.[UIR No]) Is Null));

(That last one may look a bit scary, but paste the SQL into the Access query designer and you'll see that it's really nothing to be afraid of.)

您可能感兴趣的

浅析项目中的并发 ( 一 ) 前言 控制并发的方法很多,从最基础的synchronized,juc中的lock,到数据库的行级锁,乐观锁,悲观锁,再到中间件级别的redis,zookeeper分布式锁。特别是初级程序员,对于所谓的锁一直都是听的比用的多,第一篇文章不深入探讨并发,更多的是一个入门介绍,适合于初学者,主题是“根...
Longrunning Query – xp_cmdshell? Flicking through the ol’ Solarwinds activity monitor, I spotted this one day: That four day purple thing on the right? Not good. (The remaining ...
Predatory Journals and R (This article was first published on Marcelo S. Perlin , and kindly contributed toR-bloggers) – My paper about the penetration...
Sphinx安装流程及配合PHP使用经验 1.什么是Sphinx Sphinx是俄罗斯人Andrew Aksyonoff开发的高性能全文搜索软件包,在GPL与商业协议双许可协议下发行。 全文检索式指以文档的全部文本信息作为检索对象的一种信息检索技术。检索的对象有可能是文章的标题,也有可能是文章的作者,也有可能是文章的摘要或内容...
The Learning Choice I advocate learning constantly for those of us in technology. This might be especially important for data professionals as we are at the nexus of so m...