Help With Minus Statement (difference Between Tables)

CelloBass

Distinguished
Feb 1, 2012
1
0
18,510
0
Hi everyone,
I got a problem translating a query from Oracle to Access 2010 because I don't know how to write the 'MINUS'.
I have to do the difference between two tables and I've tried using a 'LEFT JOIN' but I don't get the same result.

Basically, given two input values, the query has to show me a table with all the differences between these two tables.
Now I have this query:

SELECT value1, value2, value3, ...
FROM Table1 LEFT JOIN Table2
ON Table1.CODE = Table2.CODE
WHERE Table1.oldcode=[Please provide the oldcode:] AND Table2.newcode=[Please provide the newcode:]

As a result I get a table with more than 1.000 records but I'm expecting a table with 4 records.
Using the 'MINUS' statement on Oracle it works properly.

How can I do it?

Thank you
 

totalknowledge

Distinguished
Jul 8, 2011
56
0
18,590
3
Access isn't really designed to be SQL'ed...

You should be able to at least do some sort of sub-query with a comparison against it. Not in, or <> or something.

SELECT * FROM sometable LEFT JOIN USING (joinclause)
WHERE sometable.selector = selection AND
someothertable.negative <> (SELECT negative FROM someothertable WHERE someothertable.negativeselector = 'Boo');

Not sure of the exact syntax that it would be in ACCESS though.
 
Thread starter Similar threads Forum Replies Date
P Apps General Discussion 4
M Apps General Discussion 4
L Apps General Discussion 5
C Apps General Discussion 2
clintox Apps General Discussion 3
F Apps General Discussion 7
S Apps General Discussion 2
C Apps General Discussion 10
V Apps General Discussion 1
J Apps General Discussion 18
T Apps General Discussion 5
J Apps General Discussion 2
A Apps General Discussion 2
A Apps General Discussion 2
G Apps General Discussion 5
A Apps General Discussion 1
pologoalie8908 Apps General Discussion 8
G Apps General Discussion 1
G Apps General Discussion 3
jefe323 Apps General Discussion 3

ASK THE COMMUNITY