Access-2002 --trying to use SQL Set Operator MINUS 1

[13badar]

Hi,
I have two tables Tbl1 and Tbl2. Tble2 is a Subset of Tbl1. I want to Return the values in Tbl1 MINUS the values in Tbl2. So, I used Set Operator MINUS that [blue]"Removes the result of the second query that are also found in the first Query and only displays the rows that were uniquely returned by only the first query".[/blue]
My code Below:

SELECT A.CUST_ID, A.CUST_NAME FROM tbl1 A
MINUS
SELECT B.CUST_ID, B.CUST_NAME FROM tbl2 B;


I tried to use other SET OPERATORS e.g. UNION and UNION ALL
and they work fine. Is the operator MINUS doesnt comply with ACCESS 2002 or am I making a mistake in the SELECT Stmt.??

Also, what other ways can I achieve what I'm trying to do here. I tried to use an OUTER RIGHT JOIN Stmt. but it was returning the INTERSECT of Tbl1 and Tbl2 which is Tbl2, and I want to return Tble1 MINUS Tbl2.

 

[PHV]

SELECT A.* FROM tbl1 A LEFT JOIN tbl2 B ON A.CUST_ID=B.CUST_ID
WHERE B.CUST_ID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

I did the same thing, But it only shows two records.
For example, Tbl2 has CUST_IDs 2,3,4.
and Tbl1 has 1,2,3,4,5,6,7,8,9,10.
Query Shows only 5 and 10.
5 being the one right after 2,3,4
10 being the last one.
I dont know why this is happening.

 

[PHV]

Query Shows only 5 and 10
Can you please post the REAL SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Hey yeah I made a mistake with the second line of code when translating it for my example because I wanted certain fields to show up instead of select *. It is now working fine thanks. Sorry for the delay, I was away. Thanx for all your help.