You are here: Re: Subquery with invalid column name runs « MsSQL Server « IT news, forums, messages
Re: Subquery with invalid column name runs

Posted by Roy Harvey (SQL Server MVP) on 10/24/07 18:04

On Wed, 24 Oct 2007 17:54:28 -0000, Anne <nienna.gaia@gmail.com>
wrote:

>Thanks!
>
>But why/how is colX "available" in both tables? Maybe there are some
>basics that I don't understand...

By available I meant that there is a column with that name in the
table.

Pretty much everywhere else in SQL if there are two tables with the
same column name, and you do not qualify a reference to the column
name, an error is returned that there is an ambiguous reference. The
one exception that I know about is with a subquery. INSIDE the
subquery the rules are a little different. First it tries to resolve
the un-qualified reference using the table(s) in the FROM clause of
the subquery. If there is only one table and it has that column, that
is the particular column of that name the subquery uses. If there are
multiple tables in the subquery FROM clause and only one of them has a
column of that name, that is the particular column of that name the
subquery uses. However, if NONE of the tables in the subquery's FROM
clause has that a column with that name, THEN it will look to resolve
the unqualified column name against the tables in the outer query's
FROM clause. If there is only one such reference it uses it, if there
is more than one it will error as an ambiguous reference.

I don't know if that is any clearer, but it is more complete.

Roy Harvey
Beacon Falls, CT

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация