SQL query where clause is ambiguous

Question

Im trying to query my data with the same user_id and error occurs.

SELECT * 
FROM contacts 
JOIN users 
WHERE user_id = 1 
AND user_id = 1;

Error encountered was:

Column 'user_id' in where clause is ambiguous

Answer 1

Both the table you're pulling FROM and the table you're JOINing have a field called user_id. Specify which table you want to compare the user_id from:

SELECT * 
FROM contacts 
JOIN users 
WHERE contacts.user_id = 1
AND users.user_id = 1

Answer 2

Use an explicit JOIN with ON:

SELECT c.*, u.*  -- you should really explicitly list the columns
FROM contacts c JOIN
     users u
     ON c.user_id = u.user_id
WHERE u.user_id = 1 ;