#1052 - Column 'noMyKid' in where clause is ambiguous

Question

SELECT *, DATEDIFF(CURDATE(),tarikhmohon) AS hari
FROM mohon a 
INNER JOIN tblstatus_tak_lengkap b on a.noMyKid=b.nomykid 
WHERE noMyKid=130902100437

I have tried that SQL but I got an error.

#1052 - Column 'noMyKid' in where clause is ambiguous.

I can't find the problem.

Answer 1

Column noMyKid is present is both tables. You should tell the server on what table the column you want to filter, eg

WHERE a.noMyKid = 130902100437

or

WHERE b.noMyKid = 130902100437

Whatever you use, it doesn't matter since you are using INNER JOIN.

Answer 2

I think you have the column "noMyKid" in both tables.

Prefix it like this : a.noMyKid

Answer 3

try changing to

SELECT *, DATEDIFF(CURDATE(),tarikhmohon) AS hari
FROM mohon a 
INNER JOIN tblstatus_tak_lengkap b on a.noMyKid=b.nomykid 
WHERE a.noMyKid=130902100437

Answer 4

The problem is that mohon and tblstatus_tak_lengkap both have a noMyKid column (at least on installs with case-insensitive column names). In your WHERE clause, you need to specify which table you want to be reading the noMyKid from.

As you're joining on the value already, which table you pick doesn't matter, so something like this will work fine:

SELECT *, DATEDIFF(CURDATE(),tarikhmohon) AS hari
FROM mohon a 
INNER JOIN tblstatus_tak_lengkap b on a.noMyKid=b.nomykid 
WHERE a.noMyKid=130902100437