How do I get remaining sal from these two tables

Question

1.00/5 (1 vote)

See more:

I am having two tables

table 1 with sal details

ID ENAME SAL
1 ANU 10000
2 APARNA 20000
3 ANANYA 15000
4 ANUPAMA 40000
5 AMRUTHA 20000

table 2 with advance details

ID ADVANCE
1 1000
2 500
5 200
1 2000
2 200

What I have tried:

i want to subtract the value of advance in table2 from sal in table

output:>

ID ENAME remainin_SAL

1 ANU 7000
2 APARNA 19300
3 ANANYA 15000
4 ANUPAMA 40000
5 AMRUTHA 19800

Posted 6-Aug-18 2:37am

Member 13939442

Updated 6-Aug-18 3:04am

v2

Add a Solution

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)

littleGreenDude · Answer 1 · 2018-08-06T03:04:00

I hope I'm not doing your homework for you...

There are 2 things that make this a little tricky, first you have to construct an interim query that sums the advances and join to the main table. The second tricky thing is that you have to make sure all names are represented in the interim query so they will not be excluded by the join. This means accounting for NULLS and replacing with a ZERO advance value.

drop table #tempadvance

CREATE TABLE #tempSaldetail(
	[ID] [int] NOT NULL,
	[ENAME] [varchar](32) NOT NULL,
	[SAL] [INT] NOT NULL
) 

CREATE TABLE #tempAdvance(
	[ID] [int] NOT NULL,
	[ADVANCE] [int] NOT NULL
	)
GO

insert into #tempSaldetail select 1, 'ANU', 10000
insert into #tempSaldetail select 2, 'APARNA', 20000
insert into #tempSaldetail select 3, 'ANANYA', 15000
insert into #tempSaldetail select 4, 'ANUPAMA', 40000
insert into #tempSaldetail select 5, 'AMRUTHA', 20000

INSERT INTO #tempAdvance SELECT 1, 1000
INSERT INTO #tempAdvance SELECT 2, 500
INSERT INTO #tempAdvance SELECT 5, 200
INSERT INTO #tempAdvance SELECT 1, 2000
INSERT INTO #tempAdvance SELECT 2, 200


SELECT s.ID, ENAME, s.Sal - (adv.totalAdvance) from #tempSaldetail s
join (select sal.id, ISNULL(sum(advance),0) as totaladvance from  #tempSaldetail sal  
	left outer join #tempAdvance A on 
	sal.id = a.id group by sal.id) as adv
on adv.id = s.id