473,322 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL Join with unknown tables - but it works!?!

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.
Nov 23 '05 #1
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
--

Nov 23 '05 #2
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)
Nov 23 '05 #3
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
Nov 23 '05 #4
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.

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
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...
4
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
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
8
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,...
3
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...
12
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...
14
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...
4
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...
2
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
1
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)...
1
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....
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.