0

I'm a novice to Oracle DB, and I'm getting an error that I can't quite understand.

I have two tables, table1 and table2, with the ID primary key field in table1 being a foreign key in table2.

Here's a query that works fine:

select c.* from table1 c
    inner join table2 c2 on c.ID = c2.RID

the problem is I'm trying to write a query that's more complex, and as soon as I add more tables to my query, i get this error ORA-00904: C"."ID": invalid identifier, which I get when I run the following:

select c.* from table1 c, table3 a, table4 b
    inner join table2 c2 on c.ID = c2.RID

I've looked at this thread for help but it didn't really help my case (unless I missed something, but I doubt it)

Dan
  • 289
  • 1
  • 6
  • 15
  • 1
    Tip of today: Switch to modern, explicit `JOIN` syntax everywhere. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Dec 10 '21 at 14:18
  • 1
    Because explicit join chains are evaluated before comma separated ones... I.e. your ON only has access to table4 and table2 columns. – jarlh Dec 10 '21 at 14:19
  • 1
    Aside from your faulty join syntax it is really impossible to say without seeing the actual CREATE TABLE ddl for all tables involved. – EdStevens Dec 10 '21 at 17:56
  • It's telling you `table1` has no column called `"ID"`. I'd be inclined to believe it. A working test case would help a lot though. – William Robertson Dec 10 '21 at 20:51

1 Answers1

0

Just pile the joins on eachother like:

select c.* 
from table1 c 
    inner join table2 c2 on c.ID = c2.RID
inner join table3 a on [c.ID = a.RID] --you have not provided the relations for this 
inner join table4 b on [c.ID = b.RID] --you have not provided the relations for this

if you still get an invalid identifier from c.ID, I would double-check if ID is actually the right column name.

Please provide the table definitions and how they should relate for a better answer

W_O_L_F
  • 1,049
  • 1
  • 9
  • 16