Ambiguous column name error in Inner Join

Question

I am trying to extract information from 4 tables using inner join

For example :

TableC has the following columns:

F_name, L_Name, A_ID

TableN has the following:

Sub, Date, A_ID, N_ID, O_ID

TableM has the following columns:

Desc, Abbr, N_ID

TableO has the following:

F_name, L_Name, O_ID,

So my query which i am trying is

Select C.F_Name, C.L_Name, N.Sub, N.Date, M.Desc, M.Abbr, O.F_Name, O.L_Name
From TableN N
Inner Join TableC C
On N.A_ID = C.A_ID
Inner Join TableM M
On N.N_ID=M.N_ID
Inner Join TableO O
On O.O_ID=N.O_ID

I have described the scenario above, when I am running the query I am getting the following error:

Ambiguous column name 'F_Name'

Use the `AS` keyword to rename one of the columns which has the name `F_Name` like `C.F_Name AS CFName` — adaam, Apr 19 '14 at 17:01
Though you've specified which table they each come from, you've still selected 2 `F_Name` columns for the result set -- `C.F_Name` and `O.F_Name`. Give one or both of them a distinct alias -- `O.F_Name AS O_F_Name`. You'll also have this issue with `L_Name`. — Jonathan Lonowski, Apr 19 '14 at 17:02
Are you retrieving the result columns by name? E.g. Oracle would not issue this warning otherwise. — user2672165, Apr 19 '14 at 17:04
Possible duplicate of [Ambiguous column name error](https://stackoverflow.com/q/318066/3404097) — philipxy, Feb 26 '20 at 20:51

score 4 · Answer 1 · edited Aug 17 '21 at 23:31

4

Use aliases. Try this.

Select C.F_Name as CFNAME, C.L_Name as CLNAME, N.Sub, N.Date
    , M.Desc, M.Abbr, O.F_Name as OFNAME, O.L_Name as OLNAME
From TableN N
Inner Join TableC C On N.A_ID = C.A_ID
Inner Join TableM M On N.N_ID=M.N_ID
Inner Join TableO O On O.O_ID=N.O_ID

edited Aug 17 '21 at 23:31

Dale K

25,246
15
42
71

answered Apr 19 '14 at 17:03

sarin

5,227
3
34
63

score -2 · Answer 2 · edited Feb 26 '20 at 22:54

I had a similar problem when joining two tables. In my case, this is what I used:

public function PdfVista(){

 $this->db->select ('Clientes.idCliente, Cotizaciones.nomCotizacion, Cotizaciones.compCliente, Cotizaciones.nomProyecto,Cotizaciones.agente, Cotizaciones.fVenCotizacion, Cotizaciones.fModCotizacion, Clientes.razSocCliente, Clientes.telFijoCliente');

 $this->db->from('Cotizaciones');

 $this->db->join('Clientes', 'Clientes.idCliente=Cotizaciones.idCliente');

 $this->db->where('idCliente',10000);

 $respuesta = $this->db->get();

 return $respuesta->result();
}

but at the time of put the where, the idClient columns were repeated, both for the Clientes and Cotizaciones tables, I had to put:

$this->db->where('Clientes.idCliente',10000);

to detect the table I wanted to check

Ambiguous column name error in Inner Join

2 Answers2