Report query from Two different Database table

[murad357]

In the report i need to display something similar to below

Where Name, Age, Salary coming from one table and Phone is coming from another table (tables are in different database)

Name	Age	Salary	Phone
AAA	23	50000	1234567890
BBB	34	70000	4445556666

 

Query may look like this:

SELECT x.name AS Name, x.age AS Age, x.salary AS Salary (SELECT y.phone FROM Contact y WHERE y.id = x.id) AS Phone FROM Person x

Contact and Person table resides in two different Database.

How do I achive this ?
 

[cmatthews]

Sample,

 

SELECT

  A.Name, A.Age, A.Salary, B.Phone

FROM

  dbo.NameAgeAndSalaryTable as A

  LEFT OUTER JOIN dbo.PhoneTable as B

       ON a.ID = b.ID

 

This will return everything from table A and include phone numbers from table B where they are present. If you want to have the phone number have some default if no phone number is present, something like this (in SQL Server), the isnull/ifnull or whatever is very DB engine specific but this works for SQL Server,

SELECT

  A.Name, A.Age, A.Salary, ISNULL(B.Phone,'000-000-0000') as Phone

FROM

  dbo.NameAgeAndSalaryTable as A

  LEFT OUTER JOIN dbo.PhoneTable as B

       ON a.ID = b.ID

Hope this helps,

Clark

 

 

 

[murad357]

Hi Clark,

Thank you for your response. I was thinking the same way you did. But the problem is with the DB name. This report can be tested in different environment (Dev, UNIT, SYSTEM) with different database name in each environment ... so I can't really hard code the DB name like <DB_NAME>.dbo.<table_name>

Wondering if there is any other way to access without referencing the DB name ...

[cmatthews]

 Hey Murad,

You should be able to get the current DB name and use it, or if your connection already has set the name shouldn't be needed at all.

If I had to though... Could have a parent report and a sub report, parent report just gets the DB name, something like this in SQL Server,

DECLARE @currentDBName VARCHAR(500)

SELECT @currentDBName=DB_NAME()

Then when you call the subreport pass the @currentDBName in as a parameter and use it in your query.

SELECT * FROM $P!{currentDBName}.dbo.TableName

But depending on server DB name context may be optional all together... Dunno for you but hope this help some more.

Clark