I want a query to find all entries on table1 without a corresponding entry on table2

[landatamplex]

I have two tables of town names , in one table the spellings are correct(table x) whilst on the other there are some mistakes (table y).

I would like to run a query that will read table y and tell me all entries on it which do not have a match on table x - the towns must also be in the same county, detailes below

CREATE TABLE X
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
COUNTY_ID VARCHAR2(100 BYTE),
RATING_AREA VARCHAR2(100 BYTE),
RATING_AREA_CODE VARCHAR2(100 BYTE),
CODE_ID INTEGER
)

CREATE TABLE Y
(
ID NUMBER(10),
DESCRIPTION VARCHAR2(40 BYTE),
COUNTY_ID NUMBER(10),
ISN VARCHAR2(7 BYTE),
RATING_CODE NUMBER(10),
HOME_RATING_CODE NUMBER(10)
)

CREATE TABLE COUNTY
(
ID NUMBER(10),
DESCRIPTION VARCHAR2(40 BYTE),
RATING_CODE NUMBER(10),
HOME_RATING_CODE NUMBER(10)
)

the name field in table x should correspond with the description field in table Y, but Y are in upper case whilst X is in sentence case - the county_id on table X (which is the county name) should match the name held on a county file with an ID matching the County_ID on the Y file , the county file names are in upper case as opposed to the county name on X being in sentence

can anyone tell me what the best way to get the desired result would be

Thanks

LT

[Pilgrim333]

Hi,

What have you tried so far? At what part did you get stuck?

Pilgrim

[debasisdas]

you need to join the tables baseed on fields that you want to compare.

[landatamplex]

what I have tried so far is the following

select Y.DESCRIPTION from Y minus select UPPER (X.NAME) from X;

the problem with this is that if a town appears in more than one county then it does not check the town and county combination from file X is the same as in file Y , so if I have ballyboggin in county dublin on file Y but on file X it matches to a ballyboggin that is in county cork the above will not report this - this would be an issue if the correct spelling of the dublin town was ballybogin

[Saii]

did you try using "not exists"?

[QVeen72]

Hi,

You can also Try NOT IN:

Select * From Table1 Where Col1 Not IN (Select MyCol From Table2)

Regards
Veena