跨多个数据库连接查询-语法错误 [英] query with join across multiple databases-syntax error

问题描述

我有 2 个数据库，分别是 db1、db2.我需要一个查询来从这些 dbs(db1,db2) 中获取数据，这些 dbs(db1,db2) 每个都有 2 个表(优惠、发票).

I have 2 databases namely db1,db2. I need a query that fetch the data from these dbs(db1,db2) which have inturn 2 tables(concessions,invoicing) each.

在 db1.concessions => 中让步是主键.db1.invoicing => [Concession Number] 是主键

In db1.concessions => concession is primary key. db1.invoicing => [Concession Number] is primary key

同样在 db2.concessions => 让步是主键.db2.invoicing => [Concession Number] 是主键

similarly in db2.concessions => concession is primary key. db2.invoicing => [Concession Number] is primary key

在数据库 1 中

db1.tbl1 => Concessions 表有数据

db1.tbl1 => Concessions table has data

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

db1.tbl2 => 开票表有数据

db1.tbl2 => invoicing table has data

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

幸运的是，在数据库中，让步的价值是独一无二的.即优惠.[优惠] = 发票.[优惠编号] 不产生任何数据..

luckily, in a database the value of concession in unique. i.e concessions.[concession] = invoicing.[concession Number] yields no data..

在数据库 2 中:

db1.tbl1 => Concessions 表有数据

db1.tbl1 => Concessions table has data

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

db1.tbl2 => 开票表有数据

db1.tbl2 => invoicing table has data

    [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

此处在 db2 特许权中是唯一的，特许权.[特许权] = 发票.[特许权编号] 不产生任何数据..

HEre in db2 concession is unique, concessions.[concession] = invoicing.[concession Number] yields no data..

现在查询应该获取具有共同点的记录db1.(concessions.concession OR invoicing.concession number) = db2(concessions.concession OR invoicing.concession number)

Now the query should fetch the records that have common db1.(concessions.concession OR invoicing.concession number) = db2(concessions.concession OR invoicing.concession number)

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

In the sample data it should return, TH-123,FP-789,NZ-999, FP-675.

我的第二个问题是有可能将此查询扩展到多个数据库.我无法将数据库的数量更改为 1，因为它们已经修复.请让我知道相同的最佳程序.

My 2nd question is there is possibility of extending this query to multiple database. I can't change the count of databases to 1 as they are already fixed. Please let me know the best procedure for the same.

我试过这样的东西，有语法错误，

I tried something like this, there are syntax errors,

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 

希望你能回答这两个问题.感谢您耐心阅读这么长的邮件！

Hope you will answer both the questions. Thanks for your patience in reading such a long mail!

推荐答案

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

You can reference other databases directly if the user has permissions.

<database>.<user>.<tablename>

是数据库表的完整路径".

Is the full "path" to the database table.

经常使用

db1.dbo.tbl1 join db2.dbo.tbl2

其中数据库所有者默认为 dbo，默认情况下，任何不属于特定用户的表都由 dbo 拥有.

where dbo is default for database owner, any table not owned by a specific user is owned by dbo by default.

更新

要验证查询，您可以将其扩展到此

To get the query to validate you can expand it to this

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 

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

But I have not had time to check if this would return the right data but you can test.

这篇关于跨多个数据库连接查询-语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！