No column exists...still it works!!

Question

No column exists...still it works!!

ChiragNS

One Orange Chip

Points: 26137
More actions
September 26, 2007 at 5:18 am

#153857

Hi All
I have a simple query
Select CustomerName from Customers
WHERE CustomerState IN (SELECT State FROM Addresses).
The catch is there is no "State" column in Addresses table, but there is a column called "State" in the Customers table. :w00t:
Any ideas ?
"Keep Trying"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you don't explicitly say tablename.columnname, the engine does it's best to resolve the columns automatically, since there IS a state column in one of the two tables involved int his query,it uses Customer.State. this an example of unintended consequences of that feature.

if both tables had state in it, that's where you get the error from the compiler stating "Ambiguous column name 'state'"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

noeld SSC Guru Points: 96590 More actions · Answer 2

it is a recommended practice that when you use subqueries you prefix all column names with a table alias!

* Noel

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

Lowell

Thanks for explaining the thing to me..

Noel

I know its a good practice to prefix table names to columns.

I was just curious thats all. Thanks for your response.

"Keep Trying"