column ambiguously defined in oracle

Question

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 ?

Answer 1

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.