PHP & MYSQL: How to resolve ambiguous column names in subquery

Question

After trying to query the following nested query

SELECT ur.userID, us.fullname
FROM tbl_user_recipe AS ur JOIN tbl_user_settings AS us ON ur.userID = us.userID
WHERE relationship = 'analyzed' AND userID IN 
( SELECT ux.userID
  FROM tbl_user_recipe AS ux
  WHERE ux.relationship = 'collected'
);

I'm getting the following, and idea why?

#1052 - Column 'userID' in IN/ALL/ANY subquery is ambiguous

possible duplicate of [PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?](http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation) — M Khalid Junaid, Aug 27 '14 at 14:22

score 2 · Accepted Answer · answered Aug 27 '14 at 13:59

2

You need to prefix the alias to the table here:

WHERE relationship = 'analyzed' AND ur.userID IN

answered Aug 27 '14 at 13:59

Ende Neu

15,581
5
57
68

PHP & MYSQL: How to resolve ambiguous column names in subquery

1 Answers1