0

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!

3
  • your query uses SOL_CHEST but you haven't mentioned this table in your question Jun 9, 2015 at 14:54
  • I correct that, sorry... the tables are more complex but I tried to simplify them for the example.
    – WDrgn
    Jun 9, 2015 at 15:01
  • 1
    your inner join construct contains an on clause with 'PROD.ID' but your inner join tables are PUB, coord_pub and coord_prod, but no PROD table. The prod table will be available in a where clause (which is not present in this query)
    – miracle173
    Jun 9, 2015 at 16:00

1 Answer 1

3

you don't need to join all 4 tables, this will show the missing rows

select id, fk_id_chest, x, y from SOL_PUBLIC.COORD@RACDB
minus
select id, fk_id_chest, x, y from SIM_SOL.COORD
3
  • this query also shows rows with same id but different x,y or fk_id_chest values, these are rows that differ but not rows that are missing.. I think the question needs some clarification.
    – miracle173
    Jun 9, 2015 at 15:40
  • i was just going by the example data which implies all the data columns will be the same (when the row is there). But, yes a bit more clarification wouldn't hurt Jun 9, 2015 at 15:47
  • In that case you've only need to extend the query for your needs select id, fk_id_chest, x, y from SOL_PUBLIC.COORD@RACDB minus select id, fk_id_chest, x, y from SIM_SOL.COORD where id not in (select id from SOL_PUBLIC.COORD@RACDB)
    – vegatripy
    Jun 12, 2015 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.