We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Can I combine a WHERE and an INNER JOIN?

swpa_wnt
swpa_wnt asked
on
Medium Priority
344 Views
Last Modified: 2012-05-11
Kind of a two-part question, as I'm not sure which approach is more feasible to do what I need to do, in Access 2007.  I'm trying to find an efficient way to do the following in a single query, if possible:

- search through a table of Contracts to find those that expire within the next 4 years.
- search through the same table of Contracts to find those where a text field may contain a date string or the word(s) expire, expiration, etc.

The current query we use is only set up to search for the expiration dates, like this:

Select *
FROM CONTRACT
WHERE CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#

I am now trying to add the functionality of the string search.  To this end, I have set up a Date_Patterns table which contains parameters for the strings to look for.  In a previous EE thread, it was suggested I set up this table and do an INNER JOIN, like this:

Select *
FROM CONTRACT
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern.

So.....

#1.  What I'd like to do is be able to combine these two queries into a single query.  Is that possible?

Or....

#2.  if I can't use the INNER JOIN approach in the same query, how about something like this to put it all together:

SELECT *
FROM CONTRACT
WHERE ((CONTRACT.ExpirationDate) Between #5/1/2011# And #5/1/2015#) OR  ((CONTRACT.Notes) LIKE Date_Patterns.Pattern)

Of course in this latter approach, when I run the query I am prompted to provide a value for Patterns, probably because the SELECT statement isn't selecting anything from the Date_Patterns table.  However, I don't need the Patterns in my query results, I only need to use them to compare to Contract.Notes.  Any suggestions here?
Comment
Watch Question

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

Author

Commented:
Your last line:

and CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#


Is that supposed to be a separate WHERE clause? Or is it somehow part of the INNER JOIN, as in "INNER JOIN Statement-A and Statement-B"?

Author

Commented:
OK, I tried it as a WHERE and that seemed to work.  Thanks.

I'll be posting another question about nested INNER JOINS.

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.