Joining two tables

Hi all,
Can anyone tell how to join two tables where first table is in db1
and second table in db2..
Looking forward for ur response..
Thanx in advance...SELECT *
FROM [DB1].[dbo].[Table1] A
JOIN [DB2].[dbo].[Table2] B
ON A.[Key] = B.[Key]
--
Nik Marshall-Blank MCSD/MCDBA
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1128077633.696943.139960@.z14g2000cwz.googlegroups.com...
> Hi all,
> Can anyone tell how to join two tables where first table is in db1
> and second table in db2..
> Looking forward for ur response..
> Thanx in advance...
>|||Hi marshall...
Thanx for ur response...

Joining the table relationship from different database

Hi,
Did someone know how to join the table relationship
from different database.
DB1 - Database
Customer - Table
CustID _- Field
Cust_Name - Field
DB2 - Database
CustHist - Table
HistID - Field
Cust_ID - Field
I want to enforce the relationship for this two table in
different database. Let said, I delete one customer
record in DB1. The sql server will enforce the data
integity for me.
Any ideas, Thanks
DaronHi,
You can't enforce the foreign key relationship over two separate databases.
What you need to do is create insert and update triggers on the table and do
it that way.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> Hi,
> Did someone know how to join the table relationship
> from different database.
> DB1 - Database
> Customer - Table
> CustID _- Field
> Cust_Name - Field
> DB2 - Database
> CustHist - Table
> HistID - Field
> Cust_ID - Field
> I want to enforce the relationship for this two table in
> different database. Let said, I delete one customer
> record in DB1. The sql server will enforce the data
> integity for me.
> Any ideas, Thanks
> Daron|||Thanks, I dont think this is an efficient way to enforce
the table relationship. Because I need to write the insert
and update triggers to my related table. How about my
related tables are many. This really make me headache.
Welcome more ideas.
Daron
>--Original Message--
>Hi,
>You can't enforce the foreign key relationship over two
separate databases.
>What you need to do is create insert and update triggers
on the table and do
>it that way.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL
Scribe Documentation
>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
Server Extended
>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.
Free icon extraction
>program
>http://www.ag-software.com. Free programming tools
>
>"Daron" <darontcw@.hotmail.com> wrote in message
>news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
>> Hi,
>> Did someone know how to join the table relationship
>> from different database.
>> DB1 - Database
>> Customer - Table
>> CustID _- Field
>> Cust_Name - Field
>> DB2 - Database
>> CustHist - Table
>> HistID - Field
>> Cust_ID - Field
>> I want to enforce the relationship for this two table in
>> different database. Let said, I delete one customer
>> record in DB1. The sql server will enforce the data
>> integity for me.
>> Any ideas, Thanks
>> Daron
>
>.
>|||Daron,
There is no other way to enforce relationships over different databases.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:075001c38e10$ece30df0$a301280a@.phx.gbl...
> Thanks, I dont think this is an efficient way to enforce
> the table relationship. Because I need to write the insert
> and update triggers to my related table. How about my
> related tables are many. This really make me headache.
> Welcome more ideas.
> Daron
> >--Original Message--
> >Hi,
> >You can't enforce the foreign key relationship over two
> separate databases.
> >What you need to do is create insert and update triggers
> on the table and do
> >it that way.
> >
> >
> >--
> >I hope this helps
> >regards
> >Greg O MCSD
> >http://www.ag-software.com/ags_scribe_index.asp. SQL
> Scribe Documentation
> >Builder, the quickest way to document your database
> >http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
> Server Extended
> >Property Extended properties manager for SQL 2000
> >http://www.ag-software.com/IconExtractionProgram.asp.
> Free icon extraction
> >program
> >http://www.ag-software.com. Free programming tools
> >
> >
> >"Daron" <darontcw@.hotmail.com> wrote in message
> >news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> >> Hi,
> >> Did someone know how to join the table relationship
> >> from different database.
> >>
> >> DB1 - Database
> >> Customer - Table
> >> CustID _- Field
> >> Cust_Name - Field
> >>
> >> DB2 - Database
> >> CustHist - Table
> >> HistID - Field
> >> Cust_ID - Field
> >>
> >> I want to enforce the relationship for this two table in
> >> different database. Let said, I delete one customer
> >> record in DB1. The sql server will enforce the data
> >> integity for me.
> >>
> >> Any ideas, Thanks
> >> Daron
> >
> >
> >.
> >sql

Join two tables of different database

Hi , let table a belongs to db1 and table b belongs to db2 . how can i join these two tables can any one give me examples... thanx in advance

Quote:

Originally Posted by krishnasarma

Hi , let table a belongs to db1 and table b belongs to db2 . how can i join these two tables can any one give me examples... thanx in advance

try:

select <field list>
from db1.dbo.tableA tbla
left/inner/right/full join db2.dbo.tablB tblb on tbla.linkfiend = tblb.linkfield

dependeing on your requirement, you can choose whether it's a left, inner , full or right outer join. you might also need analyze what field to use to link the two tables