compare two tables

[palani_chamy]

i have two tables namely table1 and table2 with fields of villcode,sncode,sdcode (both tables having same fields). table1 has 1690 records and table2 has 1624 records. 1690 records of table1 includes the 1624 records of table2. now i want to filter those records found excess in table1 (ie., 66 records) by using SELECT query. Taruz of this forum suggested the following query. but it is not working.
select *
from table1
where villcode & sncode & sdcode
not in (
select (villcode & sncode & sdcode) from table2)
anybody can help me to solve this.

 

[cooh23]

i have two tables namely table1 and table2 with fields of villcode,sncode,sdcode (both tables having same fields). table1 has 1690 records and table2 has 1624 records. 1690 records of table1 includes the 1624 records of table2. now i want to filter those records found excess in table1 (ie., 66 records) by using SELECT query. Taruz of this forum suggested the following query. but it is not working.
select *
from table1
where villcode & sncode & sdcode
not in (
select (villcode & sncode & sdcode) from table2)
anybody can help me to solve this.

Try this one:

SELECT Table1.villcode, Table1.sncode, Table1.sdcode
FROM Table2 RIGHT JOIN Table1 ON (Table2.sdcode = Table1.sdcode) AND (Table2.sncode = Table1.sncode) AND (Table2.villcode = Table1.villcode)
WHERE (((Table2.villcode) Is Null) AND ((Table2.sncode) Is Null) AND ((Table2.sdcode) Is Null));

I actually like the select query that Taruz had suggested. It should work but if it doesn't work for you then try the one I did.