lookup over 2 databases and three tables

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 accountnoHave you got Linked servers up between the 2 dbs?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:123C059B-D6E8-416A-8B33-2D3B86EE2894@.microsoft.com...
> 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|||the two databases are on the same server
"Jack Vamvas" wrote:

> Have you got Linked servers up between the 2 dbs?
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:123C059B-D6E8-416A-8B33-2D3B86EE2894@.microsoft.com...
> be
> named
> by
> sord
>
>|||On Fri, 31 Mar 2006 05:32:02 -0800, Peter Newman wrote:

>ok i know i shopuld really put up all the code, but was hoping this would b
e
>a simple one i have a table called table 1 on db1 that contains fields name
d
>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 sor
d
>conde = db2.tb2 sortcode and accountno = db2.tb3 accountno
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
Hugo Kornelis, SQL Server MVP