跨多个数据库的连接查询-语法错误

Question

我有两个数据库，即db1、db2。我需要一个查询，从这些dbs(db1，db2)中获取数据，每个dbs有2个表(特许权、发票)。

在db1.特许权中，=>特许权是主要的关键。Db1.给=>特许号开发票是主密钥

类似地，在db2特许权中，=>特许权是主密钥。Db2.发票=>特许权号是主密钥

In database1

db1.tbl1 =>租让表有数据

    concession
    TH-123
    TH-456
    FP-789
    NZ-609

db1.tbl2 =>发票表有数据

    [Concession Number]          invoiced_on
    TH-322                        10.09.10
    TH-900                        23.10.10
    FP-675                        04.05.09
    NZ-111                        19.11.08

幸运的是，在数据库中，特许权的值是唯一的。即concessions.concession = invoicing.concession数不产生任何数据。

In database2:

db1.tbl1 =>租让表有数据

    concession
    TH-123
    FP-789
    NZ-999
    TH-900

db1.tbl2 =>发票表有数据

    [Concession Number]          invoiced_on(dd.mm.yy)
    TH-456                        18.01.06
    TH-777                        23.10.04
    FP-675                        03.05.09
    NZ-149                        26.11.08

HEre在db2特许权中是唯一的，concessions.concession = invoicing.concession数字不产生任何数据。

现在，查询应该获取具有公共db1的记录。(concessions.concession或invoicing.concession编号)= db2(concessions.concession或invoicing.concession编号)

在示例数据中，它应该返回TH-123、FP-789、NZ-999、FP-675.

我的第二个问题是有可能将这个查询扩展到多个数据库。我不能将数据库的计数更改为1，因为它们已经是固定的。请告诉我同样的最佳程序。

我试过这样做，有语法错误，

SELECT a.concession as db1_CON_NUMBER FROM db1.dbo.concessions as a  UNION 
SELECT b.[Concession Number] as db1_CON_NUMBER  FROM db1.dbo.invoicing as b 
INNER JOIN 
SELECT c.concession as db2_CON_NUMBER FROM db2.dbo.concessions as c  UNION 
SELECT d.[Concession Number] as db2_CON_NUMBER  FROM db2.dbo.invoicing as d 
ON db1_CON_NUMBER = db2_CON_NUMBER 

希望你能回答这两个问题。谢谢你耐心阅读这么长的一封信！

Answer 1

如果用户有权限，可以直接引用其他数据库。

<database>.<user>.<tablename>

是数据库表的完整“路径”。

你经常用

db1.dbo.tbl1 join db2.dbo.tbl2

对于数据库所有者来说，dbo是默认的，默认情况下，不属于特定用户的任何表都属于dbo。

UPDATE

要使查询验证，可以将其扩展为

SELECT * FROM 
(SELECT a.concession as db1_CON_NUMBER FROM db1.dbo.concessions as a  
UNION 
SELECT b.[Concession Number] as db1_CON_NUMBER FROM db1.dbo.invoicing as b ) c

INNER JOIN 

(SELECT c.concession as db2_CON_NUMBER FROM db2.dbo.concessions as a 
UNION 
SELECT b.[Concession Number] as db2_CON_NUMBER FROM db2.dbo.invoicing as b ) d

ON db1_CON_NUMBER = db2_CON_NUMBER 

但我还没有时间检查这是否会返回正确的数据，但您可以进行测试。

Answer 2

如果数据库位于同一个Server实例上，则可以使用3个部件命名：

database_name.schema_name.object_name

使用标识符作为对象名称

如果数据库不在同一个实例上，则创建一个链接服务器：链接服务器

创建链接服务器( Server数据库引擎)

链接服务器允许访问针对OLE数据源的分布式异构查询。创建链接服务器后，可以在此服务器上运行分布式查询，查询可以从多个数据源连接表。如果将链接服务器定义为Server的实例，则可以执行远程存储过程。

Answer 3

select * from(
SELECT a.concession as db1_CON_NUMBER FROM BABMwork6_5_3108.dbo.concessions as a  
        WHERE (a.manuell_archive_delete! = 'Delete' OR  a.manuell_archive_delete IS NULL) 
UNION SELECT b.[Concession Number] as db1_CON_NUMBER  FROM BABMwork6_5_3108.dbo.invoicing as b) as tbl1 
INNER JOIN 
(SELECT c.concession as db2_CON_NUMBER FROM BABMwork6_6_2909.dbo.concessions as c  
        WHERE (c.manuell_archive_delete! = 'Delete' OR  c.manuell_archive_delete IS NULL) 
UNION SELECT d.[Concession Number] as db2_CON_NUMBER  FROM BABMwork6_6_2909.dbo.invoicing as d ) as tbl2
ON tbl1.[db1_CON_NUMBER] = tbl2.[db2_CON_NUMBER]