sql query for tables compare

Question

0.00/5 (No votes)

See more:

Hi friends,
I am having 2 tables as follows,

Table 1

HTML

ID      Name    PayAmount
1       A        1000
2       B        2000
3       C        1000

Table 2

HTML

ID          PaidAmount
 1           600
 2           2000
 4           1000

Here i need output as follows

HTML

ID         Name   BalanceAmt
 1           A     400
 3           c     1000

Posted 18-Jan-13 20:34pm

itsureshuk

Updated 18-Jan-13 23:42pm

RDBurmon

v2

Add a Solution

2 solutions

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)

**Zoltán Zörgő** · Accepted Answer · 2013-01-18T23:54:00

Solution 2

Supposing there is only one row for an id in Table2 as in your example:

SQL

select
 t1.ID, t1.Name, t1.PayAmount-coalesce(t2.PaidAmount,0) as BalanceAmt
 from Table1 t1
 left join Table2 t2 on t1.ID = t2.ID
 where
 t1.PayAmount-coalesce(t2.PaidAmount,0) > 0
 order by t1.ID

Posted 18-Jan-13 23:54pm

Zoltán Zörgő

Comments

itsureshuk 19-Jan-13 6:25am

Thnks

RDBurmon · Accepted Answer · 2013-01-18T23:46:00

Solution 1

Here is query

SQL

SELECT T1.Id AS Id,
       T1.Name AS Name,
       (T1.PayAmount - T2.PaidAmount) AS BalanceAmt
FROM Table1 AS T1 LEFT OUTER JOIN Table2 AS T2 ON T1.Id=T2.Id
WHERE t1.PayAmount-coalesce(t2.PaidAmount,0) > 0

Hope this helps if yes then accept and vote the answer if not then revert back with your queries\
--Rahul Dhoble

Posted 18-Jan-13 23:46pm

RDBurmon

Updated 19-Jan-13 0:39am

v3

Comments

Zoltán Zörgő 19-Jan-13 5:56am

Two mistakes:
- inner join will loose id=3
- your where caluse will select rows where the balance is negative, thus there was more payed as wanted

RDBurmon 19-Jan-13 5:59am

Thanks

itsureshuk 19-Jan-13 6:10am

Thnks

itsureshuk 19-Jan-13 6:37am

rahul ,left outer join

RDBurmon 19-Jan-13 6:39am

ahh ok