oracle sqldeveloper - SQL for joining 2 tables but a bit complicated for my understanding -

got situation thats bit beyond understanding.

table has product, country , factory table b has product, factory , city.

the scenario such sales forecast data flows country level via factory , city level. have factories in rotterdam , amsterdam. issue such factories in table need same factory in table b.

i have clean data situations c&d factories in table wrong , need cleaning. therefore first need identify these wrong records:

here got far joining table , b

select a.prod,a.country,a.factory,b.prod,b.factory,b.city table1 a, table2 b , a.prod=b.prod , a.factory <>b.factory

of course can find specific known wrong record using below sql, need find wrong records without specifying product or

select a.prod,a.country,a.factory,b.prod,b.factory,b.city table1 a, table2 b a.prod=b.prod , a.factory <>b.factory , a.country ='norway' , a.factory ='rotterdam' , b.city ='oslo'

situation 1

table a

product country factory
proda switzerland rotterdam

table b

product factory city
proda rotterdam geneva

situation 2

table a

product country factory
prod germany rotterdam

table b

product factory city
prodb rotterdam dresden

situation 3

table a

product country factory
prodc norway rotterdam

table b

product factory city
prodc amsterdam oslo

situation 4

table a

product country factory
prodd finland rotterdam

table b

product factory city
prodd amsterdam helsinki

from understand projection country in table a, has city in table b, exists in country in table a.

so, in situation 1, have table country = switzerland, table b city = geneva. since, geneva in switzerland, fine in situation 2, have table country = germany, table b city = dresden since dresden in germany fine.

this gives clue on how can attack problem.

step 1. setup table expected country/city

create table country_city (country varchar(60), city varchar(60)); 

step 2. insert values expected country/city table

insert country_city(country,city) values('germany','dresden'); insert country_city(country,city) values('switzerland','geneva'); insert country_city(country,city) values('norway','oslo'); insert country_city(country,city) values('finland','helsinki'); 

step 3.

select a.prod,a.country, a.factory, b.prod, b.factory, b.city,        country_city.city  table1 inner join table2 b on a.prod=b.prod  inner join country_city on a.country = country_city.country  country_city.city = b.city , a.factory <> b.factory 

so in step 3, give database knowledge of city belongs country, can join table table b. once that, condition on not matching factories should records looking for