"Minus" keyword

[Guest]

I can't seem to get the MINUS keyword to work in Access SQL view. Is this a
limitation of using the SQL View to create a query? If so, what other keyword
do not work? (i.e., does INTERSECT work?) Is there a work around to
implement a MINUS (i cannot use VBA because I want to put this stuff using
ODBC later).

TIA,

 

[Brendan Reynolds]

There is no MINUS or INTERSECT keyword in Jet SQL - nor, as far as I can
tell, in T-SQL - at least a search of SQL Server Books Online did not turn
up any such topics.

Perhaps you want EXISTS and NOT EXISTS, or IN and NOT IN?

 

[Gary Walter]

I can't seem to get the MINUS keyword to work in Access SQL view. Is this a
limitation of using the SQL View to create a query? If so, what other
keyword
do not work? (i.e., does INTERSECT work?) Is there a work around to
implement a MINUS (i cannot use VBA because I want to put this stuff using
ODBC later).

Hi Earl,

Just to expand upon Brendan's response...

I do not use Oracle, but I have saved 2 previous posts
in case my situation might change (which might help you):

****quote*****
Use EXISTS clause to generate the same result as INTERSECT.

The following example illustrates the simulation of Oracle's INTERSECT
operator:

SELECT OrderID, OrderDate
FROM Orders O
WHERE EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)

Joe Fallon
Access MVP

Hi,

I've been trying to develop a query that would resemble an
INTERSECT query (Oracle) but Access doesn't seem to
support this type of SQL statement.

Does anyone have any ideas? I'm spending way too much time
trying to come up with a solution.

Patrick

/////////////////////////////
Newsgroups: microsoft.public.access.queries
Date: Wed, 6 Apr 2005 17:50:47 -0800
Subject: Re: Implementing Oracle's MINUS set operator in T-SQL

Hi.

Does T-SQL provide an operator that similar to minus in Oracle?

No. Very few DBMS's support the MINUS operator. Oracle does because it's
been around for so long. T-SQL (and SQL Server) are relatively young in the
database market.

Or What is the sytax to retrive the result set between the 5th record to
10th record?
For example: select top 5 to 10?

To display the 5th through the 10th top "Scores," try the following syntax:

SELECT Score
FROM (SELECT TOP 6 Score
FROM (SELECT TOP 10 Score
FROM tblTournaments
ORDER BY Score DESC)
ORDER BY Score)
ORDER BY Score DESC;

HTH.
Gunny
****unquote****

 

[Gary Walter]

I realize looking back that the previous
MINUS example is a "special case"
(in Access you try to be as creative as
you can be to not use "NOT IN/NOT EXISTS"
because they can be slow).

So, a "general case" for MINUS
(where the MINUS operator "subtracts" the results
of the second query out of the first query) might
be (using Joe's example):

SELECT OrderID, OrderDate
FROM Orders O
WHERE NOT EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)

which, *I believe*, would be accomplished
faster by the following query:

SELECT O.OrderID, O.OrderDate
FROM Orders O
LEFT JOIN
RefundsTable R
ON O.OrderID = R.OrderID
WHERE
R.OrderID IS NULL

the one time where it is appropriate
to filter on the inner (virtual) table of an outer
join.