Hello Friends,

I have the below 5 tables

1. STUDENT (STUDENT_ID, NAME)

2. DEPARTMENT (DEPT_ID, NAME, CONTACT_PERSON, PHONE)

3. SECTION (SECTION_ID,SNAME,DEPT_ID,Acad_LEVEL,SHIFT,TIME,ROOM)

4. TUITION_BILL (Seq_No,  STUDENT_ID,  DEPT_ID,  Acad_Level,  SECTION_ID,  SEMESTER,  Acad_Year, BILL_DATE,  GROSS_AMT_DUE)

5. TUITION_PAYMENT (Seq_No,RECEIPT_NO,STUDENT_ID,DEPT_ID,Acad_Level,SECTION_ID,SEMESTER,Acad_Year,SCHOLARSHIP,PAYMENT_DATE,PAYMENT_AMT,REFERENCE,REMARKS)

I wrote the following query

SELECT T.Seq_No,T.STUDENT_ID,S.NAME As STUDENT_NAME,d.name As DEPT,T.Acad_Level,c.SNAME As SECTION,

T.SEMESTER,T.[Acad_Year],BILL_DATE,GROSS_AMT_DUE,

COALESCE(SUM(T.GROSS_AMT_DUE),0)-COALESCE(SUM(PAY.PAYMENT_AMT),0)- COALESCE(SUM(PAY.SCHOLARSHIP),0) As BALANCE

FROM TUITION_BILL T JOIN STUDENT S ON S.STUDENT_ID=T.STUDENT_ID join DEPARTMENT d on d.DEPT_ID=T.DEPT_ID

join SECTION c on c.SECTION_ID=T.SECTION_ID LEFT JOIN (SELECT DISTINCT STUDENT_ID,COALESCE(SUM(p.PAYMENT_AMT),0) As PAYMENT_AMT,

COALESCE(SUM(P.SCHOLARSHIP),0) As SCHOLARSHIP FROM TUITION_PAYMENT p GROUP BY p.STUDENT_ID) As PAY ON PAY.STUDENT_ID=T.STUDENT_ID

WHERE s.STUDENT_ID='138218' GROUP BY T.Seq_No,T.STUDENT_ID,S.NAME,d.NAME,T.[Acad_Level],c.SNAME,T.SEMESTER,

T.[Acad_Year],BILL_DATE,GROSS_AMT_DUE,PAYMENT_AMT,SCHOLARSHIP

The above query shows the below output

There are two records in the TUITION_BILL table with different Semesters and bill dates for the specified student_id and there is only one record in the TUITION_PAYMENT table which is the semester one payment record. Semester two payment record is not recorded yet and I want to display the balance like the following output instead of the above output.

The above query is working fine but I'm facing only one problem with it which its showing 0 balance for both records instead of different balances like the above desired output.

Please help me in getting the desired result.

Any help would be appreciated.

Thanks in advance,

Mohamoud 

Good day Mohamoud,

Please post the queries to create the tables instead of the descriptions. We can not test or query stories but only tables :-)

In addition please post queries to insert some relevant data (relevant to your discription).

With this information we will have the option to test and re-write the query :-)