• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Can I nest INNER JOINs in sql/Access?

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.
0
swpa_wnt
Asked:
swpa_wnt
3 Solutions
 
Kent DyerCommented:
I maybe wrong in this, but I think the INNER JOIN is pretty specific to SQL Server..

You should be able to use JOIN, LEFT JOIN, RIGHT JOIN, etc.

This may also help you too (yes it is an Access 97 db, but shows some good query examples for you) - http://support.microsoft.com/kb/182568

HTH,

Kent
0
 
SharathData EngineerCommented:
try 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));

Open in new window

0
 
ralmadaCommented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ralmadaCommented:
oops some bugs 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

0
 
swpa_wntAuthor 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?
0
 
ralmadaCommented:
>>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.
0
 
ralmadaCommented:
Or probably the field is in CONTRACT_MOD_ADD not CONTRACT.... Please double check that
0
 
swpa_wntAuthor 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.
0
 
swpa_wntAuthor 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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now