That wee fact does make a bit of a difference...
Unless you have more than a few hundred rows in the error table, you are
probably doing the best you can.
If the tables are fairly large, it might be worth using an ORDER BY clause
in both queries so the rows are fetched in the same order. You skip rows in
either query that don't have a matching row in the other query. You end up
with a full-table scan of both tables, but that is usually faster than a
bunch of key lookups if you need to look at more than around 10% of the
rows, especially if the lookup is across a network.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, May 01, 2001 8:12 AM
Subject: RE: sql table joins
> Hello IIya,
> I forgot one important thing the tables are in two different databases
(that
> is why I have two connect statements). This is why I can't figure out how
to
> write the prepare statement. If they were on the same database your
> suggestions would work fine.
>
> Willie McSweeney
> Memory Component Engineer
> EMC,
> Ovens,
> Co.Cork, Ireland.
> Tel +00353-21-4281412
> Fax +00353-21-4281898
> Email <[EMAIL PROTECTED] >
>
>
> > -----Original Message-----
> > From: McSweeney, William
> > Sent: Tuesday, May 01, 2001 3:47 PM
> > To: 'Sterin, Ilya'
> > Cc: '[EMAIL PROTECTED] '
> > Subject: RE: sql table joins
> >
> > Hi IIya,
> > Thanks for the reply. I know that your suggestion will work but how do I
> > construct the prepare statement in DBI
> > 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 AML
> > database . 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???
> >
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > To: [EMAIL PROTECTED]
> > Sent: 05/01/2001 8:04 AM
> > Subject: sql table joins
> >
> > I am using perl DBI to access an sql server database.
> > One table has component part numbers that have failed, keyed in by
> > operators. I need to read in and count these which is no problem but
> > I
> > also
> > need to compare them with an approved parts table to make sure that
> > they
> > are
> > valid part numbers. Currently I am doing this by reading the failed
> > parts
> > into a hash and then using the hash keys as placeholders to query
> > the
> > parts
> > valid table.
> > Is it possible with DBI to do something like
> > SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
> > table2.valid_parts
