[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1229
  • Last Modified:

Using INTERSECT in SQL query from Access to SQL Server


I'm using Access 2003 - ADP against SQL Server 2000 using ADO

I have the following problem I'm trying to solve:
I'm trying to derive values from a single table that match month and year

field: ss_month
field: ss_year

start_month  = 10
start_year = 2005

end_month = 5
end_year = 2006

I want to select all records that are dated between start_month/start_year (e.g. 10/2005) and end_month/end_year (e.g. 5/2006)
The only way I can figure out how to do this is to do a true intersect

Select the_id from table1 WHERE ss_month >= 5 AND ss_year >= 2005
INTERSECT
Select the_id from table1 WHERE ss_month <= 6 AND ss_year <= 2006

This, however, does not work in Access.  The individual queries work fine, and they work fine when using UNION (which gives erroneous data), but the query does not work with INTERSECT.

Is there a query workaround for this?


0
Hometowncc
Asked:
Hometowncc
  • 4
  • 3
2 Solutions
 
harfangCommented:
Hello Hometowncc

I guess you will have to chop down your WHERE clause if you want to still have an optimizable query. Note that the individual queries do *not* work. The first would not return 1/2006, although I bet you think it should.

    Select the_id from table1
    WHERE ss_year = 2005 And ss_month >= 5
          OR ss_year = 2006 And ss_month <= 6

If you choose a simple query over a fast query, you can of course go:

    Select the_id From table1
    Where ss_year*100 + ss_month Between 200505 And 200606

But a criteria based on a calculation is always quite slow...

Cheers!
(°v°)
0
 
Leigh PurvisDatabase DeveloperCommented:
In an ADP you're limited by the syntax accepted by SQL Server (rather than _Access_ per se).
And T-SQL doesn't support it in that sense.  (That I know of... 2005 might?)

So you can either perform a join on two SQL Statements (much the same as an intersection when you think about it - with "In" being another obvious analogy) or use a where clause as Markus has described.
(Although as he's mentioned - your splitting of the date parts makes it non obvious).

You'd JOIN on all fields you were returning... in this case thankfully just one.

SELECT A.the_id
FROM
       (Select the_id from table1 WHERE ss_month >= 5 AND ss_year >= 2005) A
    INNER JOIN
      (Select the_id from table1 WHERE ss_month <= 6 AND ss_year <= 2006) B
    ON A.the_id = B.the_id

I'd say the Where clause is more straight forward - wouldn't you? :-)
0
 
harfangCommented:
I will dare to also correct the individual queries, to make the sample work as intended rather than as planned:

SELECT A.the_id
FROM
    ( Select the_id from table1
      Where ss_month >= 5 AND ss_year = 2005
         Or ss_year > 2005
    ) A
    INNER JOIN
    ( Select the_id from table1
      Where ss_month <= 6 AND ss_year = 2006
         Or ss_year < 2006
    ) B
    ON A.the_id = B.the_id

At least, this is what I think was intended... 14 months and not 4.
(°v°)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Leigh PurvisDatabase DeveloperCommented:
lol yeah I forgot to remove the > and <.
Ahh copy paste - my old nemesis. :-)
0
 
Leigh PurvisDatabase DeveloperCommented:
Glad you liked it  Hometowncc - but I'd have to say that what Markus had to say (both before and after me) is pretty pivotal to the overall solution.
0
 
HometownccAuthor Commented:
Thanks to harfang and lpurvis.
I'm new to this exchange, and I goofed the points.  I meant to give LPurvis assist points and harfang the Accept flag.  How do I give points for an assist, and how do I fix these points for you guys?   I thought after I clicked Accept,  I would have a chance to give assists points.
0
 
Leigh PurvisDatabase DeveloperCommented:
Post in Support (top right link) and ask for the question to be re-opened.
Then there's some option somewhere for spitting the points.

(That's really helpful huh?)
I think it's just above the Comment posting field?  Or next to submit or something.
<Markus Help Pls>
0
 
harfangCommented:
LOL. This happens often. In fact I did the same mistake when closing my first question...

You can post a short note in http:/Community_Support/ with for example:

    Please reopen question

    Please reopen {http:/Q_21877116.html} so that I can split points.
    Thanks

Once the question is reopened, the link labled "Split Points" is where Leigh thinks it is: right above the comment box.

Cheers!
(°v°)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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