query with join across multiple databases-syntax error
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!
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.
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.
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]
精彩评论