JOIN accross multiple dabases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

JOIN accross multiple dabases

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2005-07-14 : 14:59:57

Two databases on the same SQL server. This query does not work:


SELECT c.ID, c.HostName, c.DateCreated, s.FirstName, s.LastName, 
FROM DatabaseA.dbo.Companies c
INNER JOIN (SELECT DISTINCT DatabaseB.dbo.Seniors) s ON c.ID = s.ID

It gives error Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'asu_warehouse_extract.dbo' does not match with a table name or alias name used in the query.

But this query without the nested DISTINCT works fine:


SELECT c.ID, c.HostName, c.DateCreated, s.FirstName, s.LastName, 
FROM DatabaseA.dbo.Companies c
INNER JOIN Seniors s ON c.ID = s.ID

I need the distinct because Seniors table has duplicate records based on ID column. It's from a warehouse extract. Anyway, does anyone see something I'm missing or can this even be done? thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-14 : 15:10:51

you didn't specify any column names to return in your DISTINCT sub-query.

- Jeff

CactusJuice
Starting Member

46 Posts

Posted - 2005-07-15 : 11:49:01

Thanks Jeff, that's a post typo sorry. Column name is in my SQL code. Here is the code that does not work:


SELECT c.ID, c.HostName, c.DateCreated, s.FirstName, s.LastName, 
FROM DatabaseA.dbo.Companies c
INNER JOIN (SELECT DISTINCT ID FROM DatabaseB.dbo.Seniors) s ON c.ID = s.ID

Error is invalid column names: 'FirstName', 'LastName'. Yet those colmns exist. And I can run SELECT DISTINCT ID FROM DatabaseB.dbo.Seniors by itself and that works fine. Also, I remove the subquery and just JOIN Seniors table, the query works. So I must be missing something in my syntax

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-15 : 11:58:37

quote:
Originally posted by CactusJuice

Thanks Jeff, that's a post typo sorry. Column name is in my SQL code. Here is the code that does not work:

SELECT c.ID, c.HostName, c.DateCreated, s.FirstName, s.LastName,
FROM DatabaseA.dbo.Companies c
INNER JOIN (SELECT DISTINCT ID FROM DatabaseB.dbo.Seniors) s ON c.ID = s.ID

Error is invalid column names: 'FirstName', 'LastName'. Yet those colmns exist. And I can run SELECT DISTINCT ID FROM DatabaseB.dbo.Seniors by itself and that works fine. Also, I remove the subquery and just JOIN Seniors table, the query works. So I must be missing something in my syntax

Yeah -- you are missing those columns! You are only returning "ID" from the derived table. You need to return FirstName and LastName also if you want to use them in your SQL statement, which hopefully makes sense.

- Jeff

CactusJuice
Starting Member

46 Posts

Posted - 2005-07-15 : 12:09:27

Ah! I gotcha. I was think I just needed to return ID since that was my JOIN column. Good eye, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources