Column 'id_f' in where clause is ambiguous

Question

i have a join query using 3 table but i get this problem Column 'id_f' in where clause is ambiguous

$id_f=$_GET['id_f'];

$query="SELECT *, s.name AS van, st.name AS naar, p.titl, p.vname
FROM p1_users, f_confirm AS v 
INNER JOIN s_steden AS s ON v.van = s.id
INNER JOIN s_steden AS st ON v.naar = st.id
INNER JOIN p1_users AS p ON v.id_f = p.id_f
AND DATE_FORMAT(date,'%Y-%c-%d')WHERE id_f='$id_f'";
$result=mysql_query($query)or die('Wrong query : ' . mysql_error());
$row=mysql_fetch_assoc($result);

can anyone help?

Answer 1

You need to use v.id_f or p.id_f in your where clause as two tables have a column of that name so you need to disambiguate.

It actually doesn't matter which one you use in this case as you are doing an inner join.

This might not be a requirement if you use a natural join but I don't suggest using these.

Answer 2

It means, that two or more tables contain column with name "id_f"(in your case that are p1_users and f_confirm ). You need to specify for which table it related, something like this:

AND DATE_FORMAT(date,'%Y-%c-%d')WHERE p.id_f='$id_f'";

Answer 3

The problem is in WHERE clause, after DATE_FORMAT you wrote id_f='$id_f', but you have this column (id_f) in other tables in this query. So the database doesn't know wchich table you really want to use.

Answer 4

An ambiguous field is a field in a query that is in two or more tables (or subqueries).

you have two options.

1. Use table_name prefix on each ambiguous field (table1.field_x, table2.field_x)
2. Use table alias, and use the alias as prefix on each ambiguous field (alias1.x, alias2.x).

In your example, in the INNER JOIN statement you use the alias for "v.id_f = p.id_f", but then, in the WHERE clause you forget the alias.