SQL query to select record from two table using Full Join in SQL Server

RPA
 
on May 21, 2018 05:45 AM
1391 Views

Hi! I have two table with data in sql. But I want get below result.

stdId

lname

spacial

course

summoney

1

    Asrori Yatim

15010202

1

1600

2

Pulodov Rustam

15010202

1

5150

3

Sharipov Sadriddin

15010202

1

NULL

5

Soliev Firuzjon

15010202

1

NULL

 

declare @tblStudents as table(stdId int, lname varchar(100), spacial varchar(10), course int, stdActive int)
insert into @tblStudents values(1, 'Pulodov Rustam', '15010202', 1, 1)
insert into @tblStudents values(2, 'Sharipov Sadriddin', '15010202', 1, 1)
insert into @tblStudents values(3, 'Soliev Firuzjon', '15010202', 1, 1)
insert into @tblStudents values(4, 'Malaev Nurullo', '15010202', 1, 0)
insert into @tblStudents values(5, 'Asrori Yatim', '15010202', 1, 1)
select * from @tblStudents

declare @tabEgrMoney as table(id int, studId int, SumMoney decimal(18, 0), moneyType int)
insert into @tabEgrMoney values(1, 1, '1500', 0)
insert into @tabEgrMoney values(2, 1, '3500', 0)
insert into @tabEgrMoney values(3, 1, '150', 1)
insert into @tabEgrMoney values(4, 5, '1200', 0)
insert into @tabEgrMoney values(5, 5, '350', 0)
insert into @tabEgrMoney values(6, 5, '50', 1)
select * from @tabEgrMoney

select stdId, lname, spacial, course, SUM(summoney)summoney from @tblStudents s full join @tabEgrMoney m on s.stdId = m.studId where stdActive = 1 and moneyType = 0 group by stdId, lname, spacial, course

But  it's show below result:

stdId

lname

spacial

course

summoney

1

    Asrori Yatim

15010202

1

1600

5

Pulodov Rustam

15010202

1

5150

Download FREE API for Word, Excel and PDF in ASP.Net: Download
pandeyism
 
on May 22, 2018 12:58 AM

Hi RPA,

Check this example. Now please take its reference and correct your query.

SQL

SELECT stdId, lname, spacial, course,(SELECT SUM(summoney) FROM @tabEgrMoney t1 WHERE s.stdId=t1.studId AND s.stdActive=1 AND moneyType=0) as summoney
FROM @tblStudents s 
FULL JOIN @tabEgrMoney m on s.stdId = m.studId 
WHERE stdActive=1 
GROUP BY stdId, lname, spacial, course,stdActive

Output

stdId lname spacial course summoney
1 Pulodov Rustam 15010202 1 5000
2 Sharipov Sadriddin 15010202 1 NULL
3 Soliev Firuzjon 15010202 1 NULL
5 Asrori Yatim 15010202 1 1550
nagaraju60
 
on May 22, 2018 01:06 AM

Dear @RPA, 

         Please refer below query , it will help you 

 

select stdId, lname, spacial, course,(SELECT SUM(summoney) FROM @tabEgrMoney t1 WHERE s.stdId=t1.studId AND s.stdActive=1 AND moneyType=0) as summoney
from @tblStudents s full join @tabEgrMoney m on s.stdId = m.studId WHERE stdActive=1  group by stdId, lname, spacial, course,stdActive