Want to subtract two columns in two tables with different databases

Question

0.00/5 (No votes)

See more:

Hi Guys,

I want to subtract two columns from two different data base table. For example

DB1,TB1, Column 1
DB2, TB2, Column 2

want to subtract column 1 - column 2. I am trying this

i am getting an error, can you please help me

What I have tried:

SQL

Select tb1.column1, tb2.column1,
tb1.column1 - tb2.column1 AS calculation
FROM DB1.tb1 INNER JOIN DB2.TB2
ON tb1.column1 = tb2.column1

Posted 2-May-18 7:32am

Naren madi

Updated 2-May-18 17:57pm

Wendelius

v4

Add a Solution

Comments

MadMyche 2-May-18 15:02pm

What is the error?
I can tell you that the SQL you wrote would in theory always calculate to 0 based on the inner join of the columns used in the calculation

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-05-02T16:38:00

Solution 1

If the databases are on the same SQL Server instance but on different databases, you need to use three part naming when referencing the table The syntax is
[database name].[schema].[table name]
If the schema is dbo you don't need to specify it. Otherwise schema is commonly needed.

So taken that the schema is dbo, your example query could look something like

SQL

SELECT t1.column1, t2.column1, t1.column1 - t2.column1 AS calculation
FROM          DB1..TB1 t1 
   INNER JOIN DB2..TB2 t2 ON t1.column1 = t2.column1

Posted 2-May-18 16:38pm

Wendelius

Updated 2-May-18 17:58pm

v2

Comments

Naren madi 3-May-18 10:19am

Are you referring t1&t2 as tables?
and TB1 and TB2 as ??
DB1 & DB2 as database??

Can you please clarify..

Wendelius 3-May-18 23:39pm

No, t1 is an alias for the table. Alias is typically used just for convenience when referring to the table. Have a look at SQL Aliases[^]

BD1 is the database name and TB1 is the table name, just like in your example. The difference is that there are two dots between database and table name. If the schema would be specified it would look look like

...
FROM DB1.dbo.TB1 t1
...