Solved Multiple JOIN Query

[pooldead]

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.

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?

 

[theDBguy]

Hi. Just a guess, but maybe something like.

SELECT FieldName
FROM CompareApp
INNER JOIN CompareTerms
ON CompareApp.FieldName=CompareTerms.FieldName
WHERE FieldName NOT IN(SELECT FieldName FROM CompareActive)

Hope that helps...

 

[pooldead]

Hi. Just a guess, but maybe something like.

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!

 

[theDBguy]

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.