"column ambigiously defined" error

Question

I have a query like this:

SELECT *
  FROM table1 ref1,
       table1 ref2,
       table2 ref3,
       table2 ref4,
       table3
 WHERE ref3.a = ref1.b , 
       ref4.a = ref2.b , 
       ref3.c = f, 
       ref4.c = d

and it works great, it gives me 1 record with all the columns I want.

Two of these columns have the same name but the latter one gets intuitively the extension _1, so the first column with that name has the name frubberducks and the second one has the name frubberducks_1, and that's great. I need a query that only gives me these two columns so I tried:

SELECT frubberducks
  FROM table1 ref1,
       table1 ref2,
       table2 ref3,
       table2 ref4,
       table3
 WHERE ref3.a = ref1.b , 
       ref4.a = ref2.b ,
       ref3.c = f , 
       ref4.c = d

and I get the error:

ORA-00918: column ambiguously defined

Whats the best way of doing this?

Answer 1

The best way to do that is using table aliases and column aliases as below

for one column:

SELECT ref1.frubberducks
FROM table1 ref1,table1 ref2,table2 ref3,table2 ref4,table3
WHERE ref3.a = ref1.b and ref4.a = ref2.b and ref3.c=f and ref4.c=d

for two columns with the same name:

SELECT ref1.frubberducks,
       ref2.frubberducks
FROM table1 ref1,table1 ref2,table2 ref3,table2 ref4,table3
WHERE ref3.a = ref1.b and ref4.a = ref2.b and ref3.c=f and ref4.c=d

for two columns with the same name and with column aliases:

SELECT ref1.frubberducks ref1frubberducks,
       ref2.frubberducks ref2frubberducks
FROM table1 ref1,table1 ref2,table2 ref3,table2 ref4,table3
WHERE ref3.a = ref1.b and ref4.a = ref2.b and ref3.c=f and ref4.c=d

Answer 2

The best way is to be explicit such as with:

SELECT ref1.frubberducks as frubberducks,
       ref3.frubberducks as frubberducks_1 ...

select * means you want everything and you're not overly concerned about where it is in the result set. If you did care, you'd be explicitly listing the columns. There are precious few cases where you should actually use select * anyway.

Answer 3

It's because if you specify which column you want to get in return, you have to specify them unambiguously.

So it should look like

SELECT ref1.frubberducks, ref2.frubberducks as frubberducks_1
FROM table1 ref1,table1 ref2,table2 ref3,table2 ref4,table3
WHERE ref3.a = ref1.b , ref4.a = ref2.b , ref3.c=f , ref4.c=d