accidental DBA needing help

Question

accidental DBA needing help

cw.izatt

Right there with Babe

Points: 776
More actions
June 1, 2011 at 1:45 pm

#252070

I am the classic accidental DBA at a medium size company and i have a join question. If this is not the correct spot for this post please move it, Mods.
I am trying to join tables from two different databases. here is a simple example that i cannot get to work and i get the error: The column prefix 'Table1' does not match with a table name or alias name used in the query. I have also tried a left join as well as listing two columns to narrow it down. Please point me in the right direction. I may be going about this the wrong way basically i am needing to link data from different DB's together with a select statement for a conversion and i am a little lost as to why i can join within a DB but not two or more DB's together.
Thanks in advance
Chris

select * from DB1.dbo.table1 inner join DB2.dbo.Table2 on table1.acctnumber = table2.acctnumber

Never stop learning or you will be left behind.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

unless you alias the table, you must use the whole 3 part naming convention(Database.Schema.Table.ColumnName) toi identify the columns in the joins:

here's the right way, and then again with aliases for the tables:

select *

from DB1.dbo.table1

inner join DB2.dbo.Table2

on DB1.dbo.table1.acctnumber = DB2.dbo.Table2.acctnumber

select *

from DB1.dbo.table1 MyAliasA

inner join DB2.dbo.Table2 MyAliasB

on MyAliasA.acctnumber = MyAliasB.acctnumber

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!

LutzM SSC Guru Points: 107049 More actions · Answer 2

You're using the table name only in your join predicate: table1.acctnumber = table2.acctnumber

But those table names are interpreted as being local tables.

The easiest would be to assign an alias to the three-part-table name:

select * from DB1.dbo.table1 table1 inner join DB2.dbo.Table2 Table2 on table1.acctnumber = table2.acctnumber

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

cw.izatt Right there with Babe Points: 776 More actions · Answer 3

thanks guys for the quick response, i tried that before, but when i tried it i was doing my joins out of order. silly mistake.

also as a side note any suggestions on good SQL books for somebody in my situation?

Never stop learning or you will be left behind.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

For reallllllllly basic you can go to w3schools.

After that you can read and search problems on this forum... and then ask questions for what you don't find.

cw.izatt Right there with Babe Points: 776 More actions · Answer 5

true and i am sure i will be searching the forum in the future as i run into new SQL and SQL server problems.

Never stop learning or you will be left behind.

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 6

cw.izatt (6/1/2011)
also as a side note any suggestions on good SQL books for somebody in my situation?

Check BOL - Books On Line, it comes with SQL Server for free. If in need of some deeper digging try Microsoft Knowledge Base.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

cw.izatt Right there with Babe Points: 776 More actions · Answer 7

BoL has been downloaded thanks for that.

Never stop learning or you will be left behind.