Column 'column name' in Where clause is ambiguous

Question

I’m trying to ride a left join where I want the results of a particular code. My SQL is:

$resultado = mysql_query("SELECT CA.DAT_EHORA_EVENT, CA.TXT_NOMEX_EVENT, CA.MEM_DESCR_EVENT FROM tbl_CLIENTES C LEFT JOIN tbl_CLIENTES_AGENDA CA ON CA.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN WHERE COD_IDENT_CLIEN = '".$COD_IDENT_CLIEN."'") or die(mysql_error());

The mistake that’s coming back is:

Column 'COD_IDENT_CLIEN' in where clause is ambiguous

Answer 1

This error is quite simple, the column COD_IDENT_CLIEN exites in both tables and therefore needs to be specified in the clause WHERE, so put

WHERE CA.COD_IDENT_CLIEN = ...

or

WHERE C.COD_IDENT_CLIEN = ...

Answer 2

What happens is that you have two columns called COD_IDENT_CLIEN (one on the table tbl_CLIENTES and the other on the table tbl_CLIENTES_AGENDA) and Mysql has no way of knowing which of the two you are referring to in your WHERE:

WHERE COD_IDENT_CLIEN

Once you are specifying CA.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN then whatever the column would be chosen, because the two are equal. So, you can fix this using any of them:

WHERE C.COD_IDENT_CLIEN

And your code goes like this:

$resultado = mysql_query("SELECT CA.DAT_EHORA_EVENT, CA.TXT_NOMEX_EVENT, CA.MEM_DESCR_EVENT FROM tbl_CLIENTES C LEFT JOIN tbl_CLIENTES_AGENDA CA ON CA.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN WHERE C.COD_IDENT_CLIEN = '".$COD_IDENT_CLIEN."'") or die(mysql_error());

And by the way, watch out for the injection of SQL. Your code is suffering from this problem. Use the Prepared statements PHP to solve this problem.

Answer 3

The problem is that you have two columns called COD_IDENT_CLIEN: C.COD_IDENT_CLIEN and CA.COD_IDENT_CLIEN.

I know you specified CA.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN, then it is logical that when you refer to COD_IDENT_CLIEN, either of the two columns will do, but unfortunately the SQL standard does not allow DBMS to make this inference.

In some Sgbds, you could write

FROM tbl_CLIENTES C NATURAL LEFT JOIN tbl_CLIENTES_AGENDA CA

and then not only would JOIN be done automatically, matching all columns with equal names in the two tables, but conceptually the result would have only one column called COD_IDENT_CLIEN, and then your WHERE would work the way you expect.

(Unfortunately, Mysql is not one of these systems - already considered the possibility of migrating to Postgresql?)