We help IT Professionals succeed at work.

Can I nest INNER JOINs in sql/Access?

swpa_wnt
swpa_wnt asked
on
Medium Priority
683 Views
Last Modified: 2012-05-11
In Access 2007, is it possible to nest INNER JOINS in a single query?

here's the gist of my query:

We have contracts and contract mods stored in separate tables (one to many relationship, where CONTRACT.ContractNumber = CONTRACT_MOD.ParentContractNumber).
I need to select contract mods where: a) the expiration date is empty or within a certain date range; b) the mod has one of several strings appearing within a field called Notes; and c) the parent contract is not expired.

I have set up the text string parameters in a table called Date_Patterns so I can do an INNER JOIN with that table (CONTRACT_MOD.Notes LIKE Date_Patterns.Pattern).  However......... To get the proper set of mods and parent contract data, I first need to do an INNER JOIN between CONTRACT and CONTRACT_MOD.  Thus, my query looks something like this:

SELECT *
FROM CONTRACT_MOD
INNER JOIN CONTRACT On CONTRACT.ContractNumber = CONTRACT_MOD.ParentContractNumber
INNER JOIN Date_Patterns On CONTRACT_MOD.Notes LIKE Date_Patterns.Pattern
WHERE ((((CONTRACT_MOD_ADD.ExpirationDate) = "") OR ((CONTRACT_MOD_ADD.ExpirationDate) Between #5/1/2011# And #5/1/2015#)) AND (CONTRACT.ExpirationInd)=No));


But I'm getting syntax errors on the "union query" -- even though there is no explicit union query.  Is there a proper syntactical way to nest these INNER JOINS?

Thanks for any direction.
Comment
Watch Question

Kent DyerApplication Systems Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
SharathData Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
try adding some brackets and also for the patterns you migh consider adding "*" there
SELECT * 
FROM (CONTRACT_MOD
INNER JOIN CONTRACT On CONTRACT.ContractNumber = CONTRACT_MOD.ParentContractNumber)
INNER JOIN Date_Patterns On CONTRACT_MOD.Notes LIKE "*" & Date_Patterns.Pattern * "*"
WHERE (
	(
	((CONTRACT_MOD_ADD.ExpirationDate) = "") OR 
	((CONTRACT_MOD_ADD.ExpirationDate) Between #5/1/2011# And #5/1/2015#)
	) AND 
	(CONTRACT.ExpirationInd)=No)
      );

Open in new window

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
kdyer,

That would explain why Access sometimes complains about the INNER JOIN.  lol  However, it does seem to run it okay.  I used to work in SQL, so I just naturally used it.

Sharath and ralmada,

You all have suggested very similar code.  So here's what I've got now:

====================================
SELECT *

FROM ((CONTRACT_MOD_ADD

INNER JOIN CONTRACT
         On CONTRACT.ContractNumber = CONTRACT_MOD_ADD.ParentContractNumber)

INNER JOIN Date_Patterns
         On CONTRACT_MOD_ADD.Notes LIKE Date_Patterns.Pattern)

WHERE (
(
((CONTRACT_MOD_ADD.ExpirationDate) = "") OR
((CONTRACT_MOD_ADD.ExpirationDate) Between #5/1/2011# And #5/1/2015#)
) AND
((CONTRACT.ExpirationInd)=No)
      );
====================================

This might work -- but I can't tell, as I just encountered another problem.  The WHEN statement includes a condition for CONTRACT.ExpirationInd... but CONTRACT.ExpirationInd is NOT being selected in the query, so Access asks me to supply a value for it when I run the query.  It appears I need to somehow pull that into my set.  If I explicitly include CONTRACT.ExpirationInd in my SELECT statement, would that sort of override the need for the INNER JOIN with the CONTRACT table?

Commented:
>>so Access asks me to supply a value for it when I run the query<<

Please check the spelling of the field in your query. Usually Access ask your for a parameter when it doesn't recognize the field as such.

Commented:
Or probably the field is in CONTRACT_MOD_ADD not CONTRACT.... Please double check that

Author

Commented:
Ah, yes, the field is ExpiredInd, not ExpirationInd.

I've got to leave work, but will report back here tomorrow if I see the results are still not right.  Thanks for your help so far.

Author

Commented:
Well, it appears we may be going with a new table to facilitate our reporting solution, since the table structure we have now doesn't really track the data we need -- and this new approach will negate my query development thus far.

But generally I think I had it working, so I'll be awarding points.  Thanks for the help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.