Hi all,
While debugging some old code from someone, I came across this stored
procedure:
SELECT dbo.TBL_COORD.COORD_ID AS ID, dbo.TBL_COORD.LATITUDE AS Latitude,
dbo.TBL_COORD.LONGITUDE AS Longitude,
dbo.TBL_COORD.NORTHING AS Northing,
dbo.TBL_COORD.EASTING AS Easting, dbo.refDROP_VALUES.Drop_Value AS [Geometry
Type],
refDROP_VALUES_1.Drop_Value AS [GPS Datum],
refDROP_VALUES_2.Drop_Value AS [GPS Used]
FROM dbo.TBL_COORD INNER JOIN
dbo.refDROP_VALUES ON dbo.TBL_COORD.GEOMETRYTYPE_ID =
dbo.refDROP_VALUES.ID INNER JOIN
dbo.refDROP_VALUES refDROP_VALUES_1 ON
dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID INNER JOIN
dbo.refDROP_VALUES refDROP_VALUES_2 ON
dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID
WHERE
<some conditions here>
This query seems to work fine, however I cannot see ANY source to the tables
refDROP_VALUES_1, and refDROP_VALUES_2. There are no views/tables/stored
procedures of any kind with these names in the databse, so I'm at a loss as
to where they're coming from. Note that there IS a table refDROP_VALUES, and
the fields that refDROP_VALUES_1 and refDROP_VALUES_2 reference ARE fields
in the table refDROP_VALUES. I can view the results from running the query.
Whats going on here? Does MS SQL create these tables?
Jack. 4 1801
refDROP_VALUES_1 and refDROP_VALUES_2 are referenced first as owner
(schema) names, specifically the owner names of the two tables called
Drop_Value.
Those same names (refDROP_VALUES_1 and refDROP_VALUES_2) are then used
as aliases for another table owned by dbo (dbo.refDROP_VALUES). This is
very confusing naming but is perfectly legal if all the tables exist.
To demonstrate, try:
SELECT TOP 10 * FROM refDROP_VALUES_1.Drop_Value
SELECT TOP 10 * FROM refDROP_VALUES_2.Drop_Value
SELECT TOP 10 * FROM dbo.refDROP_VALUES
Lookup Owner Names in Books Online if you haven't come across two-part
names before.
--
David Portas
SQL Server MVP
--
On Mon, 14 Nov 2005 21:26:51 +0800, Jack wrote: Hi all, While debugging some old code from someone, I came across this stored procedure:
SELECT dbo.TBL_COORD.COORD_ID AS ID, dbo.TBL_COORD.LATITUDE AS Latitude, dbo.TBL_COORD.LONGITUDE AS Longitude, dbo.TBL_COORD.NORTHING AS Northing, dbo.TBL_COORD.EASTING AS Easting, dbo.refDROP_VALUES.Drop_Value AS [Geometry Type], refDROP_VALUES_1.Drop_Value AS [GPS Datum], refDROP_VALUES_2.Drop_Value AS [GPS Used] FROM dbo.TBL_COORD INNER JOIN dbo.refDROP_VALUES ON dbo.TBL_COORD.GEOMETRYTYPE_ID = dbo.refDROP_VALUES.ID INNER JOIN dbo.refDROP_VALUES refDROP_VALUES_1 ON dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID INNER JOIN dbo.refDROP_VALUES refDROP_VALUES_2 ON dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID WHERE <some conditions here>
This query seems to work fine, however I cannot see ANY source to the tables refDROP_VALUES_1, and refDROP_VALUES_2. There are no views/tables/stored procedures of any kind with these names in the databse, so I'm at a loss as to where they're coming from. Note that there IS a table refDROP_VALUES, and the fields that refDROP_VALUES_1 and refDROP_VALUES_2 reference ARE fields in the table refDROP_VALUES. I can view the results from running the query. Whats going on here? Does MS SQL create these tables?
Jack.
Hi Jack,
In addition to David's comments, this query _requires_ the use of table
aliases. That's because the same table (dbo.refDROP_VALUES) is joined in
three times. Without aliasing, that would result in three copies of the
same table existing in the work result set, and there would be no way to
know which of the three you refer to if you use a column name.
I find it more understandable to never leave out the optional AS keyword
between table name and alias. I also believe that there would be less
confusion if in this case all three occurences of dbo.refDROP_VALUES had
been aliased.
SELECT some columns
FROM dbo.TBL_COORD
INNER JOIN dbo.refDROP_VALUES AS refDROP_VALUES_0
ON dbo.TBL_COORD.GEOMETRYTYPE_ID = refDROP_VALUES_0.ID
INNER JOIN dbo.refDROP_VALUES AS refDROP_VALUES_1
ON dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID
INNER JOIN dbo.refDROP_VALUES AS refDROP_VALUES_2
ON dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID
WHERE <some conditions here>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes: I find it more understandable to never leave out the optional AS keyword between table name and alias. I also believe that there would be less confusion if in this case all three occurences of dbo.refDROP_VALUES had been aliased.
And even less confusing if the alias had been short, like DV1, DV2 etc.
In my opinion using alias is a necessity in most cases, as if you alwyas
write out the table names in full, it's difficult to see the forest
for the trees.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thank-you to all for your comments... David - I will be looking into this
to increase my MS SQL knowledge as I've never come across this concept
before!
Thanks,
Jack.
"Jack" <ja*******@nospam.co.uk> wrote in message
news:43********@quokka.wn.com.au... Hi all, While debugging some old code from someone, I came across this stored procedure:
SELECT dbo.TBL_COORD.COORD_ID AS ID, dbo.TBL_COORD.LATITUDE AS Latitude, dbo.TBL_COORD.LONGITUDE AS Longitude, dbo.TBL_COORD.NORTHING AS Northing, dbo.TBL_COORD.EASTING AS Easting, dbo.refDROP_VALUES.Drop_Value AS [Geometry Type], refDROP_VALUES_1.Drop_Value AS [GPS Datum], refDROP_VALUES_2.Drop_Value AS [GPS Used] FROM dbo.TBL_COORD INNER JOIN dbo.refDROP_VALUES ON dbo.TBL_COORD.GEOMETRYTYPE_ID = dbo.refDROP_VALUES.ID INNER JOIN dbo.refDROP_VALUES refDROP_VALUES_1 ON dbo.TBL_COORD.GPS_DATUM = refDROP_VALUES_1.ID INNER JOIN dbo.refDROP_VALUES refDROP_VALUES_2 ON dbo.TBL_COORD.GPS_USED = refDROP_VALUES_2.ID WHERE <some conditions here>
This query seems to work fine, however I cannot see ANY source to the tables refDROP_VALUES_1, and refDROP_VALUES_2. There are no views/tables/stored procedures of any kind with these names in the databse, so I'm at a loss as to where they're coming from. Note that there IS a table refDROP_VALUES, and the fields that refDROP_VALUES_1 and refDROP_VALUES_2 reference ARE fields in the table refDROP_VALUES. I can view the results from running the query. Whats going on here? Does MS SQL create these tables?
Jack.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: StealthBananaT |
last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the
count of its reviews, MySQL locks and I have to restart the...
|
by: thilbert |
last post by:
All,
I have a perplexing problem that I hope someone can help me with.
I have the following table struct:
Permission
-----------------
PermissionId
Permission
|
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
|
by: xixi |
last post by:
when i create a join view like this
create view JV104FZ.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
JV104FZ.APPTINM.APAM32,...
|
by: Martin |
last post by:
Hello everybody,
I have the following question.
As a join clause on Oracle we use " table1.field1 = table2.field1 (+) "
On SQL Server we use " table1.field1 *= table2.field1 "
Does DB2...
|
by: Phil Powell |
last post by:
<cfquery name="getAll" datasource="#request.dsn#">
SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn,
U.choiceId, U.experience, T.label AS teamLabel, R.label AS
roleLabel
FROM User U...
|
by: Jim Andersen |
last post by:
I have a problem with this standard employee-supervisor scenario
For pictures:
http://www.databasedev.co.uk/self-join_query.html
I want to show all employees "belonging" to a specific...
|
by: Jane T |
last post by:
I appreciate how difficult it is to resolve a problem without all the
information but maybe someone has come across a similar problem.
I have an 'extract' table which has 1853 rows when I ask for...
|
by: frederikengelen |
last post by:
Hello all,
We are seeing strange behaviour for queries on a table we need to
convert data from. We try to find out whether table A(B_CONV_ID)
contains data that does not exists in table...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |