Search code examples
sqldatabaseoracleora-00904

Oracle DB - ORA-00904: "invalid identifier" error on select

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)

Solution

  • 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