I have 4 tables in a Oracle database:
SOL_PUBLIC.SOL_CHEST@RACDB (PUB):
ID ...
100 ...
200 ...
300 ...
SOL_PUBLIC.COORD@RACDB (PUB):
ID FK_ID_CHEST x y
1 100 11 11
2 200 22 22
3 300 33 33
SOL_CHEST (PROD)
ID ...
100 ...
200 ...
300 ...
SIM_SOL.COORD (PROD)
ID FK_ID_CHEST x y
1 100 11 11
so, some rows are missing from PROD part. I want to get all rows missing from PROD.COORD table that are in PUB.COORD table.
I first tried to get all 4 tables "in the same place"
select *
from SOL_CHEST PROD,
SOL_PUBLIC.SOL_CHEST@RACDB PUB
inner join SOL_PUBLIC.COORD@RACDB coord_pub
on coord_pub.FK_ID_CHEST=PUB.ID
inner join SIM_SOL.COORD coord_prod
on coord_prod.FK_ID_CHEST=PROD.ID
but it gives me
[Error] Execution (67: 40): ORA-00904: "PROD"."ID": invalid identifier
how to do this (connecting the 4 tables and get the rows missing from prod.coord that are in pub.coord) Thanks!