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
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.