3

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
mary
  • 149
  • 1
  • 5
  • 10

4 Answers4

3

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'";
vmg
  • 9,920
  • 13
  • 61
  • 90
  • @mary Don't forget to accept the answer. Click the tick underneath the score on the left. Do this for all of your questions and people will be more likely to answer for you. :) – Brendan Bullen Jul 13 '11 at 15:45
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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

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.

fdaines
  • 1,216
  • 10
  • 12
0

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.

sennin
  • 8,552
  • 10
  • 32
  • 47