If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Minus" keyword
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, -- Earl Takasaki Master of Financial Engineering Candidate University of California at Berkeley |
#2
|
|||
|
|||
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? -- Brendan Reynolds (MVP) "Earl Takasaki" wrote in message news 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, -- Earl Takasaki Master of Financial Engineering Candidate University of California at Berkeley |
#3
|
|||
|
|||
"Earl Takasaki" wrote: 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 "Patrick" wrote 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: 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**** |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can we use join keyword without using INNER keyword explicitly in. | S.SRIKANTH,GUDIWADA,AP,INDIA | Running & Setting Up Queries | 1 | April 20th, 2005 02:07 PM |
AND Operator in Keyword Search Query | darrep | Running & Setting Up Queries | 3 | March 23rd, 2005 08:23 PM |
Keyword search in Access | Kerry | Running & Setting Up Queries | 1 | January 14th, 2005 01:13 PM |
keyword search in Access | lavesely | Database Design | 1 | October 23rd, 2004 01:38 PM |
Formula Calculating Minus Values | Russ | Worksheet Functions | 2 | October 6th, 2003 03:06 PM |