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

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