SQL query to select records from two tables using SubQuery in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

i want to display total remaining fees which is due on the student before leaving the school for example it may be even 100 Rupees

table structure

StudentTable

AdmissionNo

SName

FName

Status

R-001

ABC

SKY

Active

R-002

SKY

ABC

Active

R-003

XYZ

MNO

In Active

 

FeesCollectionTable

AdmissionNo

VoucherNo

Total

Paid

RemBal

Month

R-001

VC-0001

1000

700

300

July

R-002

VC-0001

1000

800

200

July

R-001

VC-0003

1300

1200

100

Aug

R-002

VC-0004

1200

1200

0

Aug

 before leaving the school i want to see the fees status of the student in this format

Student Leaving Page

AdmissionNo

SName

FName

VoucherNo

Paid

RemBal

Status

R-001

ABC

SKY

VC-0003

1200

100

Active

R-002

SKY

ABC

VC-0004

1200

0

Active

R-003

XYZ

MNO

NULL

NULL

NULL

In Active

i wrote query

SELECT fc.AdmissionNo,SName,FName,Status,RemBal FROM tblFeesCollection as fc inner join tblStdReg as sr on fc.AdmissionNo=sr.AdmissionNo WHERE FeeID IN (SELECT MAX(FeeID) FROM tblFeesCollection GROUP BY AdmissionNo );

it is not showing recording according to my requirements

You are viewing reply posted by: dharmendr one year ago.
Posted one year ago

Hi smile,

Refer below test query.

SQL

DECLARE @tblStdReg AS TABLE(AdmissionNo VARCHAR(10),SName VARCHAR(10),FName VARCHAR(10),Status VARCHAR(10))
INSERT INTO @tblStdReg VALUES('R-001','ABC','SKY','Active')
INSERT INTO @tblStdReg VALUES('R-002','SKY','ABC','Active')
INSERT INTO @tblStdReg VALUES('R-003','XYZ','MNO','In Active')
                 
DECLARE @tblFeesCollection AS TABLE(AdmissionNo VARCHAR(10),VoucherNo VARCHAR(10),Total INT,Paid INT,RemBal INT)
INSERT INTO @tblFeesCollection VALUES('R-001','VC-0001',1000,700,300)
INSERT INTO @tblFeesCollection VALUES('R-002','VC-0001',1000,800,200)
INSERT INTO @tblFeesCollection VALUES('R-001','VC-0003',1300,1200,100)
INSERT INTO @tblFeesCollection VALUES('R-002','VC-0004',1200,1200,0)

SELECT	sr.AdmissionNo
		,sr.SName
		,sr.FName
		,(SELECT TOP 1 fc.VoucherNo FROM @tblFeesCollection fc WHERE fc.AdmissionNo = sr.AdmissionNo ORDER BY VoucherNo DESC) VoucherNo
		,(SELECT TOP 1 fc.Paid FROM @tblFeesCollection fc WHERE fc.AdmissionNo = sr.AdmissionNo ORDER BY VoucherNo DESC) Paid
		,(SELECT TOP 1 fc.RemBal FROM @tblFeesCollection fc WHERE fc.AdmissionNo = sr.AdmissionNo ORDER BY VoucherNo DESC) RemBal
		,Status
FROM @tblStdReg sr

Output

AdmissionNo SName FName VoucherNo Paid RemBal Status
R-001 ABC SKY VC-0003 1200 100 Active
R-002 SKY ABC VC-0004 1200 0 Active
R-003 XYZ MNO NULL NULL NULL In Active