ORA-00918: column ambiguously defined in SELECT *

Question

Getting ORA-00918: column ambiguously defined: running this SQL:

SELECT * FROM   (SELECT DISTINCT(coaches.id),     people.*,     users.*,     coaches.*   FROM "COACHES"   INNER JOIN people ON people.id = coaches.person_id   INNER JOIN users ON coaches.person_id = users.person_id   LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id ) WHERE rownum <= 25 

Any suggestions please?

Answer 1

A query's projection can only have one instance of a given name. As your WHERE clause shows, you have several tables with a column called ID. Because you are selecting * your projection will have several columns called ID. Or it would have were it not for the compiler hurling ORA-00918.

The solution is quite simple: you will have to expand the projection to explicitly select named columns. Then you can either leave out the duplicate columns, retaining just (say) COACHES.ID or use column aliases: coaches.id as COACHES_ID.

Perhaps that strikes you as a lot of typing, but it is the only way. If it is any comfort, SELECT * is regarded as bad practice in production code: explicitly named columns are much safer.