June 15, 2005 at 1:03 pm
I am new to TSQL and am writing a fairly simple select statement in which I am using two separate databases (on the same server) but am unsure how to pull them in Query Analyzer. Can someone point me in the right direction?
Thanks very much, in advance!!!!
Austin
June 15, 2005 at 1:06 pm
USE DB1
Select * from db2.dbo.TableName T1 inner join dbo.Table2 T2 on T1.id = T2.id
June 15, 2005 at 1:14 pm
Thanks very much, Remi! I must stress, however, that I am truly new to TSQL. What I tried earlier without success was the following statement is Query Analyzer:
Select rapdata.member.member_number, rapdata.member.Last_name, legalHotline.call.member_number, legalHotline.call.CallDate
From Rapdata.Member, LegalHotline.Call
Where Rapdata.Member.Member_Number=LegalHotline.Call.Member_Number
I'm not sure how to differentiate between the two different databases.
Thanks again for your kind help!
Austin
June 15, 2005 at 1:25 pm
SELECT tblSaleAgents.SaleAgentEmployeeID,
TempTest.dbo.EMPPF.EMPNUMBER, tblSalesAgents.SalesAgentMInitial,
TempTestCareers.dbo.EMPPF.MINAME, tblSalesAgents.SalesAgentStatus,
TempTestCareers.dbo.EMPPF.EMPSTAT
FROM tblSales LEFT OUTER JOIN TempTest.dbo.EMPPF ON dbo.tblSalesAAgents.SalesAGentFName = TempTest.dbo.EMPPF.EMFNAM AND
tblSalesAAgents.SalesAgentLName = TempTestCareers.dbo.EMPPF.EMLNAM
tblSaleAgents is the local database table
EMPPF is the second database table
So, the syntax is as the prvious answer:
databasename.dbo.tablename.columnname
Good luck
June 15, 2005 at 1:27 pm
you can use this technic to change database if you only want to run a select :
--start script
use Pubs
Select * from jobs
use NorthWind
Select * from Orders
--or the equivalient from a third db :
use master
Select * from Pubs.dbo.jobs
use NorthWind
Select * from Northwind.dbo.Orders
--end script
June 15, 2005 at 1:45 pm
Thank you both for your kind assistance and extreme patience! I think I must be VERY thick, because I am just not getting it. Here are two examples of what I have tried without any success:
select member.member_number, member.last_name, legalhotline.call.member_number, legalhotline.calldate
from member, legalhotline.call
where member.member_number=legalhotline.dbo.call.member_number
Use Rapdata
select member.member_number, member.last_name
from rapdata.dbo.member
Use legalhotline
from legalhotline.dbo.call
select call.member_number, call.calldate
In the second example, I am not really sure how to create the join parameters either. For example, I want to be able to join on the member_number. urgghhhhhh...thanks for your patience, guys!!! Any additional help you might provide would be greatly appreciated. Thanks again!!!!!
Austin
June 15, 2005 at 1:54 pm
Here's a working exemple. The data makes no sens but at least the query works :
Select * from NorthWind.dbo.SysObjects O inner join Pubs.dbo.SysColumns C on O.id = C.id
June 15, 2005 at 5:42 pm
Austin, Looking at the given query below this won't work. You will get error like invalid object name on legalhotline.call
/*
select member.member_number, member.last_name, legalhotline.call.member_number, legalhotline.calldate
from member, legalhotline.call
where member.member_number=legalhotline.dbo.call.member_number
*/
assuming your 2 databases are on the server, it should look something like this:
you need to provide dbname.owner.table_name in the FROM clause
Eg.
select T1.*, T2.*
from dbname1.dbo.table_1 T1, dbname2.dbo.table_2 T2
Where T1.id = T2.id
"It's always a good practice to include the owner of the table" ie. dbo.table_name
Just a personal opinion, if you're good at using the WHERE clause stick to it. No need to use inner join clause. It shouldn't be too much differences in term of performance wise.
June 15, 2005 at 7:18 pm
Thank you all so very much for your kind assistance! I finally GOT IT! I really appreciate your taking time to explain it to me.
Thanks very much!
Austin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply