If the tables are in two different databases on the same server, the naming
format is:
Database.Owner.Table.
therefore you can do a join in MS like this:
SELECT value1 FROM database1.dbo.table1 l
JOIN database2.dbo.table2 r ON l.somecolumn = r.somecolumn.
(dbo is the default owner. if dbo is the owner, you can reference it as:
Database..Table)
If they are on two different servers, there are two ways to do this in MS
SQL (If you are usnig 7.0 or later, one way using 6.5)
The first way is to create a linked server definition. This is not
difficult, but is a little more involved than I want to go into for this
message. (Read the SQL Server Books Online that are installed with MS SQL to
get the instructions for creating Linked server definitions). Once the
Linked server definition is created, you can to a distributed query using a
4 part naming convention that is SERVER.DATABASE.OWNER.TABLE and it is a
simple as
SELECT Value1 FROM Myserver.Database1..Table1 l
JOIN Otherserver.Database2..Table2 r on l.somecolumn = r.somecolumn.
If you are working ad-hoc in 7.0 or later, you can use an openrowset
function and join to the results in the openrowset function. You can do that
from PERL, but if it is something you will use frequently, just create a
linked server rather than use the OPENROWSET.
All of these things are contained in MS SQL Server Books Online, but I'll
lend a hand if you need more help getting started.
Sincerely,
Steve Howard
PS. Don't use the old SQL Syntax for joins - it limits what you can do with
SQL too much.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 10:38 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: sql table joins
Hi Sumit,
The databases are both microsoft sql. As you say it looks like DBI does not
support what I would like to do when the tables are in different databases.
I will just have to continue what I have right now which is read in from
one, store in a hash and the use the hash keys to query the second database.
the only problem with this is that it ran a little slow and I felt getting
SQL to do this compare would be faster.Thanks any way
Willie
Willie McSweeney
Memory Component Engineer
EMC,
Ovens,
Co.Cork, Ireland.
Tel +00353-21-4281412
Fax +00353-21-4281898
Email <[EMAIL PROTECTED] >
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, May 01, 2001 4:24 PM
> To: [EMAIL PROTECTED]
> Subject: RE: sql table joins
>
>
>
>
>
>
> Hello Willie,
>
> >If I do something like
> >$dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
> >"could not CONNECT $!";
> >$dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die
> "could
> >not CONNECT $!";
> >
> >$sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
> >AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
> >AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die
> "Couldn't
> >prepare statement: " . $dbh->errstr;
> >
> >I get an error "invalid object name AMLMaster". This is I guess because
> $dbh
> >is the connection to TotalBoards and knows nothing about the AMLMaster
> >table. I have another $dhh_aml connecting to AMLMaster. My question is
> how
> >do you write a prepare statement as above that works. Can you get $dbh to
> >connect to both tables at the same time???
> >Willie
>
> If I guess it correctly, the table AMLMaster is in the AML Database/DSN
> and
> TotalBoards Database/DSN, right. If so you will have to have a database
> link created between the 'TotalBoards' and 'AML' databases. I don't think
> you can do what you are doing with out a database link or some thing like
> that between 'TotalBoards' and 'AML'. Also i don't think ODBC supports
> such
> a thing.
>
> What databases are these two? If it's oracle then you can create a
> database
> link using the command 'CREATE DATABASE LINK ...' in SQL Plus.
>
> Hope this helps.
>
> Regards,
>
> Sumit.
> **************************************************************************
> ******************************
>
> Just because something doesn't do what you planned it to do doesn't mean
> it's useless.
> -Thomas A. Edison
> **************************************************************************
> ******************************
>
