Click here to Skip to main content
14,326,246 members
Rate this:
Please Sign up or sign in to vote.
Please assist me I am stuck with this SQL statement. I am a beginner with SQL, I am developing a C# school management software.

This is my Select query:
SELECT distinct RTRIM(Students.StudentNumber),RTRIM(Students.StudentSurname),RTRIM(Students.StudentFirstNames),RTRIM(CourseFeePayment.PaymentDue) from Students,CourseFeePayment,CourseFeePayment_Join where Students.StudentNumber=CourseFeePayment.StudentNumber and CourseFeePayment.CourseFeePaymentID=CourseFeePayment_Join.C_PaymentID and CourseFeePayment.SchoolYear=@d1 and CourseFeePayment.Student_Class=@d2 and CourseFeePayment_Join.Month=@d3 and CourseFeePayment.PaymentDue > 0 order by 2

        cmd.Parameters.AddWithValue("@d1", cmbAcademicYear.Text);
        cmd.Parameters.AddWithValue("@d2", cmbClass.Text);
        cmd.Parameters.AddWithValue("@d3", cmbMonth.Text);
        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        while ((rdr.Read() == true))
         dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4));

I need this to return a list per row of each students who partially owes from this specific class only for this specific month only. By Partially I mean if the class fee is for example $100 for January, if the student hasn't paid anything (owing full amount) must be ignored and only list students owing from greater than 0 to less than 100 in this case.

This query only works well when I have only one student and return the correct Payment Due. If I have many students in the same month, it returns them but with wrong Payment Due amount. If I have a student who has paid in installments like (paid more than once) but hasn't finished, it returns each installment of the same student on a different line with different Payment Due amount.

What I have tried:

Any help will be appreciated. Here are my tables:
Students Table:
StudentID (primary)

CourseFeePayment Table:
CourseFeePaymentID (primary)

CourseFeePayment_Join Table:
Updated 1-Oct-19 15:05pm
Rate this:
Please Sign up or sign in to vote.

Solution 3

First things first; it is always helpful to post code that has some formatting to it, which makes it a lot easier for people to read what you are trying to do
SELECT distinct
,	RTRIM(Students.StudentSurname)
,	RTRIM(Students.StudentFirstNames)
,	RTRIM(CourseFeePayment.PaymentDue)
from 	Students
,	CourseFeePayment
,	CourseFeePayment_Join 
where	Students.StudentNumber			= CourseFeePayment.StudentNumber 
and	CourseFeePayment.CourseFeePaymentID	= CourseFeePayment_Join.C_PaymentID
and	CourseFeePayment.SchoolYear		= @d1
and	CourseFeePayment.Student_Class	= @d2
and	CourseFeePayment_Join.Month		= @d3
and	CourseFeePayment.PaymentDue		> 0
order by 2
For my next trick.... I'm going to eliminate the RTrims as it does nothing for numbers, and the text should have had it done at the time of insertion. I am also going to use some aliases on the table names, and I'm going to write in actual joins utilizing them.
Notice I can get the whole SELECT list neatly on one line
SELECT DISTINCT s.StudentNumber, s.StudentSurname, s.StudentFirstNames, p.PaymentDue

FROM	Students					s
INNER JOIN CourseFeePayment			p ON s.StudentNumber	= p.StudentNumber 
INNER JOIN CourseFeePayment_Join	j ON p.CourseFeePaymentID= j.C_PaymentID

WHERE	CourseFeePayment.SchoolYear		= @d1
AND		CourseFeePayment.Student_Class	= @d2
AND		CourseFeePayment_Join.Month		= @d3
AND		CourseFeePayment.PaymentDue		> 0

Now I can see a problem forming with this query.... What this is actually going to do is pull a list of students who had a payment due and also made a payment in the selected month.

What we really are going to need to do is aggregate the amounts that are paid, by student, for the selected period
SELECT	p.StudentNumber, SUM(p.TotalPaid)
FROM	CourseFeePayment		p
INNER JOIN CourseFeePayment_Join	j ON p.CourseFeePaymentID = j.C_PaymentID
WHERE	p.StudentClass	= @d2
AND		j.Month			= @d3
GROUP BY p.StudentNumber
I really don't want to go too much further with this; as I do not know any of the data that you do have and what mechanisms are in place to INSERT payment records- this is not a design I would be using. Depending on what is all in going on would tell me if I was going to use this aggregation as a subquery, store it in a #temp table, or use it once via a CTE

What I would recommend is working with this in SSMS or some other SQL client application, as it would be much more versatile and convenient.
I would also recommend that when all is said and done with this query that you just convert it over to a Stored Procedure.
katela 1-Oct-19 22:06pm
Many thanks for your insight. I will try to implement it this way and see the results.
Maciej Los 2-Oct-19 3:00am
Good job!
Rate this:
Please Sign up or sign in to vote.

Solution 1

you need have the sql statment in the execute.
you close the connection after read rows/output


string tempValue;
                   string connStr = "server=localhost;user=root;database=mytestmydb;port=nnnn;password="+ppwd.ToString();
                   MySqlConnection conn = new MySqlConnection(connStr);

               DateTime dtnow = DateTime.Now;
               Console.WriteLine("Connecting to MySQL... time: "+ dtnow);

                       string sql = "select * from books;";
                       MySqlCommand cmd = new MySqlCommand(sql, conn);
               MySqlDataReader mmreader = cmd.ExecuteReader();

               ////data rows
               while (mmreader.Read())
                   tempValue = mmreader.GetString("author");


                   catch (Exception ex)

katela 2-Oct-19 10:03am

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100