Search code examples
sqljoinambiguousname-collision

column ambiguously defined in oracle


I am no sql expert. I was trying to run following query on my oracle sqlplus prompt

select 0 AS initVal, 
loadTable.amount from 
accountsTable JOIN loadTable ON num=accNum ,
loadTable JOIN loanTable ON loadTable.numSeq=loanTable.numSeq

and getting following error

column ambiguously defined, which is pointing to loadTable.amount in select clause.

Little bit I am able to understand that there is a cartesian product of accountsTable JOIN loadTable ON num=accNum and loadTable JOIN loanTable ON loadTable.numSeq=loanTable.numSeq after which it is not able to understand from which table to pick loadTable.amount, since result of both has this column. Is there any way I can get rid of this problem without specifying another join between these two intermediate tables on loadTable.amount ?


Solution

  • This is your query:

    select 0 AS initVal, loadTable.amount
    from accountsTable JOIN
         loadTable
         ON num=accNum ,
         loadTable JOIN
         loanTable
         ON loadTable.numSeq=loanTable.numSeq;
    

    Your query has loadtable twice, which I don't think you intend. And, the last condition is a tautology, because the value is coming from the same table. Also, you should use table aliases that are abbreviations -- it makes queries easier to write and read. I think this is closer to what you are trying to do:

    select 0 AS initVal, lt.amount
    from accountsTable act JOIN
         loadTable lt
         ON act.num = lt.accNum JOIN
         loanTable lot
         ON lt.numSeq = lot.numSeq;
    

    As a general rule: Never use comma in from statements. Always connect the tables by the appropriate join.

    EDIT:

    If you think your query is "logically" correct, you should put in table aliases and be specific about the joins:

    select 0 AS initVal, lot1.amount
    from accountsTable act JOIN
         loadTable lot1
         ON act.num = lot1.accNum CROSS JOIN
         loadTable lot2 JOIN
         loanTable lnt
         ON lot1.numSeq = lot2.numSeq;
    

    Those join conditions look non-sensical to me, especially because the query pulls the amount from only one table, so the second table doesn't even seem to be used.