ok i know i shopuld really put up all the code, but was hoping this would be
a simple one i have a table called table 1 on db1 that contains fields named
sortcode, accountno, licence

on DB 2 i have two tables  tb2 tb3 , tb2 has a field called sortcode and a
fiedl called id
tbl3 has a field called accountno and id. records in tb2 & 3 are linked by
the id field, what i need to do is find the licence from db1 wwhere the sord
conde = db2.tb2 sortcode and accountno = db2.tb3 accountno

Have you got Linked servers up between the 2 dbs?



Show quoteHide quote

the two databases are on the same server

Show quoteHide quote

On Fri, 31 Mar 2006 05:32:02 -0800, Peter Newman wrote:

Hi Peter,

Putting up all the code is really better. Especially since it allows us
to copy and paste your CREATE TABLE and INSERT statements and test our
proposed solutions, then check the results against your posted required
output. This also helps prevent misunderstandings about the
requirements. And finally, it clarifies the EXACT column names (in your
post, you're refering to one column as "sortcode" or "sord conde").

Oh, and BTW: "table 1" is a poor choice of tablename - the use of a
space in the name forces you to quote the name, which is frankly quite a
pain.

Anyway, here's an untested guess:

SELECT     T1.licence
FROM       db2.tb2
INNER JOIN db2.tb3
      ON   tb3.id = tb2.id
INNER JOIN db1."table 1" AS T1
      ON   T1.sortcode = tb2.sortcode
      AND  T1.accountno = tb3.accountno

BUG or not?
Gradual Performance Degradation
passing empty string to stored procedure -SQL Express 2005
Seek clever solution to detect deletion
SET ANSI_NULLS and SET QUOTED_IDENTIFIER
Autoincrement in varchar value
DataBase Design Problem....
fileLen function in stored procedure
select with numbering
SQL and MSAccess - copy