BorlandTalk.com

DA Brookes · Guest

Hi

I have two databases..

1...a project directory with company details in (eg company name) in a
database named ms_projdir in a table named "wclist"
2...A cost report (in a db named cost_report, table named m19c)

Database 2 needs to "look up" company details from DB1.

three fields make a unique company reference, con_no, group_no and wc_no

Is there (via sql) any way I can "join" the above three fields to lookup the
company name(etc) without having to traverse the resulting set using
calculated fields... which is slow...

I think it might be something like....

use cost_report
select con_ref,group_no,wc_no from m19c
cross join ms_projdir.wclist
on wclist.con_ref = m19c.con_ref,
on wclist.group_no = m19c.group_no
on wclist.wc_no = m19c.group_no

how do I extract the description field (wc_desc) and insert it into the
result?
etc...

Regards

Dave

DA Brookes · Guest

Hi Edo

Thanks for your help... this worked... (thanks to you)

SELECT DB1_m19c.CON_REF, DB2_wclist.group_no, DB1_m19c.WC_NO,
DB2_wclist.WC_DESC
FROM M19c DB1_m19c INNER JOIN
ms_projdir.DBO.wclist DB2_wclist ON DB1_m19c.CON_REF =
DB2_wclist.CON_REF COLLATE SQL_Latin1_General_CP1_CI_AS AND
DB1_m19c.WC_NO = DB2_wclist.WC_NO COLLATE
SQL_Latin1_General_CP1_CI_AS AND
DB1_m19c.GROUP_no = DB2_wclist.group_no COLLATE
SQL_Latin1_General_CP1_CI_AS

Regards

Dave

edo · Guest

try something like :

select con_ref,group_no,wc_no from COST_REPORT.DBO.m19c DB1_m19c
cross join ms_projdir.DBO.wclist DB2_wclist
on DB2_wclist.con_ref = DB1_m19c.con_ref,
on DB2_wclist.group_no = DB1_m19c.group_no
on DB2_wclist.wc_no = DB1_m19c.group_no

you can even do it accross servers with:
sp_droplinkedsrvlogin

edo.

"DA Brookes" <david_dot_brookes_at_interserveprojects.com> wrote