How to avoid "Ambiguous field in query" without adding Table Name or Table Alias in where clause

mysql, mysql-error-1064, sql

Solution

If you for some reason can't live with doing

select T1.name, T1.address, T1.phone, T2.title, T2.description from T1
Left Join T2 on T1.CID=T2.ID
where T2.STATUS = 1

Then I guess you could

SELECT T1.name, T1.address, T1.phone, T2.title, T2.description 
FROM (  SELECT CID, name, address, phone
        FROM T1) AS T1
LEFT JOIN T2
ON T1.CID=T2.ID
WHERE STATUS = 1

Basicly just skip getting the STATUS column from T1. Then there can be no conflict.

Bottomline; there's no simple way of doing this. The one closest to simple would be to have different names of both STATUS columns, but even that seems extreme.

Problem

I have a select query in which I have joined a couple of tables say `T1` and `T2` and both the tables have a field named STATUS which I don't need to fetch. In the where clause I need to add `WHERE STATUS=1` and some more conditions. But somehow I just can't add the table name or table alias to the field in the where clause i.e. I can't use where `T2.STATUS=1`. Is there any way to always consider the `STATUS=1` from the where clause being `T1.STATUS` so that I can avoid "Ambiguous field error"? Here is a sample query: ``` select T1.name, T1.address, T1.phone, T2.title, T2.description from T1 Left Join T2 on T1.CID=T2.ID where STATUS = 1 ``` In above query, I want the `STATUS =1` to always mean `T2.STATUS`

Original source