query with join across multiple databases-syntax e

问题:

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.

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

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

In database1

db1.tbl1 => Concessions table has data

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

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..

In database2:

db1.tbl1 => Concessions table has data

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

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

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

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

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

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!

回答1:

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

<database>.<user>.<tablename>

Is the full "path" to the database table.

Often you use

db1.dbo.tbl1 join db2.dbo.tbl2

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

UPDATE

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.

回答2:

If the databases are on the same SQL Server instance you can use 3 part naming:

database_name.schema_name.object_name

Using Identifiers As Object Names

If the databases are not on the same instance, create a linked server: Linked Servers

Creating Linked Servers (SQL Server Database Engine)

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

回答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]