Query throws ORA-00904 Invalid Identifier when using joins

Question

I've come across some weird behavior that's preventing me from setting up a query in the way I'd like to. Would appreciate any ideas. If there's any information about the tables that would be helpful, please let me know. I looked through them and nothing jumped out at me that might cause this but nor did I know what I was looking for. Here is the behavior.

This works fine:

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b,
    SCHEMA_C.TABLE_C c,
    SCHEMA_A.TABLE_D d
Where 
    b.friend_id = c.friend_id
    AND a.group_id = d.group_id
    AND b.group_cd = d.group_cd

But this returns ORA-00904: b.friend_id = c.friend_id: invalid identifier

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b,
    SCHEMA_A.TABLE_D d
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id
Where 
    a.group_id = d.group_id
    AND b.group_cd = d.group_cd

This returns ORA-00904: b.group_cd = d.group_cd: invalid identifier

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id
Join 
    SCHEMA_A.TABLE_D d
On 
    a.group_id = d.group_id
    AND b.group_cd = d.group_cd

And this works again:

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id
Join 
    SCHEMA_A.TABLE_D d
On
    b.group_cd = d.group_cd
Where  
    a.group_id = d.group_id

Answer 1

Try with the using keyword which is designed for join on same column name

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C USING(friend_id)
Join 
    SCHEMA_A.TABLE_D d using(group_id)
where b.group_cd = d.group_cd;

Also make sure that you execute the other query with the right users, as an user that does not have correct permission will throw invalid identifier.

Edit : The actual problem is that you are joining TABLE_C with TABLE_D but the join condition refer to TABLE_B change it to

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_A.TABLE_D d,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C c using(friend_id)
Where 
    a.group_id = d.group_id
    AND b.group_cd = d.group_cd;

Apply the same logic for other queries, when doing a join, you do not join to a group of table, but to the last table mentionned in the from clause therefore it did not have access to TABLE_B.

For other people that might have an invalid identifier error, make sure your database is not case sensitive, else make sure to use the right case.

Answer 2

I believe that you have run into the intricacy in SQL dealing with join scoping/precedence. Since I am not myself a database programmer, I can't really tell you why it works the way it does, but I do see some logic in the way the query parser is behaving.

In your first query, all the joins are cross joins. As a result, they should have the same precedence. So all the joined columns are known when the join is evaluated.

In the second query, you have a combination of cross joins and inner joins. If we assume that inner join has precedence over cross joins, then table c and d are joined before any other tables are added to the mix. So when you reference b.friend_id in the inner join, which is outside of the inner join, the query parser isn't able to use that column in evaluating the join.

In the third query, the inner join between tables b, c and d take precedence over the cross join. So column a.group_id isn't available in evaluating the inner join condition. When you take table a out of the inner join condition in the final query, the query no longer has a conflicting precedence.