[quoted text, click to view] 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
--
