Solved Multiple JOIN Query

pooldead

Registered User.
Local time
Today, 07:53
Joined
Sep 4, 2019
Messages
136
I have the following tables (anonymized):

Compare_App:
Name
John Smith

Compare_Active:
Name
John Smith

Compare_Terms:
Name
John Smith

I am trying to write a multi-join query that says Select the names from "Compare_App" that also appear in "Compare_Terms" and exclude those that appear in "Compare_Active". The goal here being to identify terminated employees on a user list, but excluding any employees that may have been rehired. I have the following query, but it does not accomplish the exclusion yet.

Code:
SELECT [Full Name]
FROM (Compare_App
INNER JOIN Compare_Terms ON Compare_Terms.Name LIKE '*' & Compare_App.Name & '*')
LEFT JOIN Compare_Active ON Compare_Active.Name LIKE '*' & Compare_App.Name & '*';

Can someone assist?
 
Hi. Just a guess, but maybe something like.
SQL:
SELECT FieldName
FROM CompareApp
INNER JOIN CompareTerms
ON CompareApp.FieldName=CompareTerms.FieldName
WHERE FieldName NOT IN(SELECT FieldName FROM CompareActive)
Hope that helps...
 
Hi. Just a guess, but maybe something like.
SQL:
SELECT FieldName
FROM CompareApp
INNER JOIN CompareTerms
ON CompareApp.FieldName=CompareTerms.FieldName
WHERE FieldName NOT IN(SELECT FieldName FROM CompareActive)
Hope that helps...
That did the trick! I had tried something like that previously, but kept a join in the NOT IN statement. Thanks for your help as always!
 
That did the trick! I had tried something like that previously, but kept a join in the NOT IN statement. Thanks for your help as always!
Hi. You're welcome. Glad to hear it worked for you. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom