Column 'column name' in where clause is ambiguous

3

I am trying to mount a left join where I want the results of a certain 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 error that is returning is:

Column 'COD_IDENT_CLIEN' in where clause is ambiguous
    
asked by anonymous 24.06.2015 / 21:31

3 answers

5

This error is very simple, the column COD_IDENT_CLIEN exists in both tables and therefore needs to be specified in the WHERE clause, so place

WHERE CA.COD_IDENT_CLIEN = ...

or

WHERE C.COD_IDENT_CLIEN = ...

    
24.06.2015 / 21:40
3

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

WHERE COD_IDENT_CLIEN

Since you are specifying CA.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN , then both columns are the same, since both are the same. So you can fix this using any of them:

WHERE C.COD_IDENT_CLIEN

And your code looks 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 beware of SQL Injection . Your code is suffering from this problem. Use prepared statements from PHP to solve this problem.

    
24.06.2015 / 21:42
2

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

I know that you have specified CA.COD_IDENT_CLIEN = C.COD_IDENT_CLIEN , so it stands to reason that when you refer to COD_IDENT_CLIEN , either column serves, but unfortunately the SQL standard does not allow the DBMS to make this inference. >

In some DBMS, 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, as conceptually the result would have only one column named COD_IDENT_CLIEN , and then its WHERE would work the way it You wait.

(Unfortunately, MySQL is not one of these systems - have you considered migrating to PostgreSQL?)

    
24.06.2015 / 21:43