June 30, 2015 at 8:52 am
Hi everyone. I have the following SQL query written for an Access database, but have bene told that NOT IN is very efficient unless you are simply listing values. I'd therefore like to re-write it.
SELECT Clients.ReportName, Consultants.ShortName AS [Consultant]
FROM
Clients
LEFT JOIN Consultants ON Clients.ConsultantRef = Consultants.ConsultantRef
WHERE Clients.ClientRef NOT IN
(
SELECT Clients.Clientref
FROM (((((Clients
LEFT JOIN Consultants ON Clients.ConsultantRef = Consultants.ConsultantRef)
LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef)
LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef)
LEFT JOIN CommnEntries ON CommnEntries.CommnPremRef = CommnPremiums.CommnPremRef)
LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)
WHERE (ABS(Policies.OnRisk)=1 AND ABS(Policies.Status)=1)
AND CommnEntries.Type IN (3, 5)
)
AND Clients.ClientRef NOT IN
(
SELECT Clients.Clientref
FROM ((((((((Clients
LEFT JOIN Consultants ON Clients.ConsultantRef = Consultants.ConsultantRef)
LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef)
LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef)
LEFT JOIN CommnEntries ON CommnEntries.CommnPremRef = CommnPremiums.CommnPremRef)
LEFT JOIN ACOffsets ON ACOffsets.FKeyRef = CommnEntries.CommnEntryRef)
LEFT JOIN ACOffsetLinks ON ACOffsetLinks.ACOffsetRef = ACOffsets.ACOffsetRef)
LEFT JOIN Fees ON ACOffsetLinks.FeeRef = Fees.FeeRef)
LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)
WHERE (ABS(Policies.OnRisk)=1 AND ABS(Policies.Status)=1)
AND Fees.TypeOfService = 3
)
ORDER BY Clients.ReportName
I started experimenting with NOT EXISTS, but this doesn't appear to work. For example, I took the first NOT IN sub query and tried to turn it into a basic NOT EXISTS. But the below doesn't return any records.
SELECT * FROM Clients
WHERE NOT EXISTS
(
SELECT Clients.ClientRef
FROM (((((Clients
LEFT JOIN Consultants ON Clients.ConsultantRef = Consultants.ConsultantRef)
LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef)
LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef)
LEFT JOIN CommnEntries ON CommnEntries.CommnPremRef = CommnPremiums.CommnPremRef)
LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)
WHERE CommnEntries.Type IN (3, 5)
GROUP BY Clients.ClientRef
The subquery part returns one record when run on its own. The database has 4 client records, so I would expect the results to show 3 records. But it returns nothing. Am I misunderstanding how NOT EXISTS works?
Sorry if all this isn't clear but I'm not the best at MS Access syntax.
Regards
Steve
June 30, 2015 at 9:42 am
All those outer joins in sub-queries are enough to confuse anyone.
Try something like:
SELECT C.ReportName, E.ShortName AS Consultant
FROM Clients C
LEFT JOIN Consultants E
ON C.ConsultantRef = E.ConsultantRef
WHERE NOT EXISTS
(
SELECT 1
FROM Policies P
WHERE P.ClientRef = C.ClientRef
AND ABS(P.OnRisk) = 1
AND ABS(P.[Status]) = 1
ANDEXISTS
(
SELECT 1
FROM CommnEntries CE
WHERE EXISTS
(
SELECT 1
FROM CommnPremiums CP
WHERE CP.CommnPremRef = CE.CommnPremRef
AND CP.PolicyRef = P.PolicyRef
)
AND
(
CE.[Type] IN (3, 5)
OR EXISTS
(
SELECT 1
FROM ACOffsets AO
WHERE AO.FKeyRef = CE.CommnEntryRef
AND EXISTS
(
SELECT 1
FROM ACOffsetLinks AL
WHERE AL.ACOffsetRef = AO.ACOffsetRef
AND EXISTS
(
SELECT 1
FROM Fees F
WHERE F.FeeRef = AL.FeeRef
AND F.TypeOfService = 3
)
)
)
)
)
);
If you cannot get it to work then post sample data, in consumable format, along with the results you expect for the sample data.
As this is a Microsoft SQL Server site, I am assuming your backend is SQL Server.
June 30, 2015 at 10:08 am
Hi Ken. That's great, many thanks for that. Much appreciated. The backend is actually Access, but I use these forums for SQL Server queries, so assumed this particular forum seection was specifically for Access queries. If not, I apologise.
Regards
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply