如何添加"INNER JOIN"到现有的“不存在"；询问?.查询在下面的描述中 [英] How can add "INNER JOIN" to an existing "NOT EXIST" Query?. The query is in the description below

问题描述

我有一个现有的Ms Access查询，可以根据我的需要很好地工作，现在我想联接另外两个表("Taxes_Tariffs" 和"complaints" >)都具有公共列"Tenant_ID" 和 Bill _#

I have an existing Ms Access query which works just fine as per my need, now I want to join another two tables ("Taxes_Tariffs" and "complaints") Which all have a common column "Tenant_ID" and Bill_#

有人可以帮助我解决将此附加功能包括在下面的现有查询中的问题吗?非常感谢您的贡献.谢谢

Can someone help me out with the way forward to include this additional functionality to the existing query below? I will highly appreciate for your contributionS. Thanks

SELECT BillSummary1.*
FROM BillSummary AS BillSummary1
WHERE NOT EXISTS ( 
SELECT 1 FROM BillSummary AS BillSummary2
WHERE (BillSummary2.[Tenant Name] = BillSummary1.[Tenant Name])
AND (BillSummary2.[Current Reading Date] > BillSummary1.[Current Reading Date]
OR   (BillSummary2.[Tenant Name] = BillSummary1.[Tenant Name])
AND BillSummary2.ID > BillSummary1.ID)
)

推荐答案

您可以在 BillSummary2 内部联接在不存在中>例如.

You may add the inner join in the not exist just after BillSummary2 eg.

SELECT BillSummary1.*
FROM BillSummary AS BillSummary1
WHERE NOT EXISTS ( 
SELECT 1 FROM BillSummary AS BillSummary2

INNER JOIN
    Taxes_Tariffs ON Taxes_Tariffs.Tenant_ID = BillSummary2.Tenant_ID AND
                     Taxes_Tariffs.Bill_NO =  BillSummary2.Bill_NO
INNER JOIN
    complaints ON complaints.Tenant_ID = BillSummary2.Tenant_ID AND
                     complaints.Bill_NO =  BillSummary2.Bill_NO

WHERE (BillSummary2.[Tenant Name] = BillSummary1.[Tenant Name])
AND (BillSummary2.[Current Reading Date] > BillSummary1.[Current Reading Date]
OR   (BillSummary2.[Tenant Name] = BillSummary1.[Tenant Name])
AND BillSummary2.ID > BillSummary1.ID)
)

如果您希望在初始选择中包括这些列，则可以使用以下内容:

if you would like to include these columns in your initial select you may use the following:

SELECT 
    BillSummary1.*,
    Taxes_Tariffs.*,
     complaints.*
FROM BillSummary AS BillSummary1
INNER JOIN
    Taxes_Tariffs ON Taxes_Tariffs.Tenant_ID = BillSummary1.Tenant_ID AND
                     Taxes_Tariffs.Bill_NO =  BillSummary1.Bill_NO
INNER JOIN
    complaints ON complaints.Tenant_ID = BillSummary1.Tenant_ID AND
                     complaints.Bill_NO =  BillSummary1.Bill_NO
WHERE NOT EXISTS ( 
SELECT 1 FROM BillSummary AS BillSummary2



WHERE (BillSummary2.[Tenant Name] = BillSummary1.[Tenant Name])
AND (BillSummary2.[Current Reading Date] > BillSummary1.[Current Reading Date]
OR   (BillSummary2.[Tenant Name] = BillSummary1.[Tenant Name])
AND BillSummary2.ID > BillSummary1.ID)
)

这篇关于如何添加"INNER JOIN"到现有的“不存在"；询问?.查询在下面的描述中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！