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

5
votes

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

1
mysqlsqlmysql-error-1064
What's the problem with using alias? Let's try to fix it :) - hgulyan
I guess your simplified query does not contain the problem. Could you show the real query? - juergen d
This query does contain the problem because it does not know whether it is STATUS in T1 or T2. - Raouf Athar
But you tried T1.Status and it did not work, right? - juergen d
Actually this query is built in runtime in a php function. The Where clause is supplied as a parameter to the function and it can't be hard coded. There are many conditions in the where other than STATUS=1 or STATUS =0. I can't use the alias because I have called this function numerously throughout my project and instead of adding alias to every function call, I thought it would be better to modify the actual function once only. - Raouf Athar

1 Answers

3
votes

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.