I use VB.NET + Mysql.
I have two tables (tblstudents & tblpayments). I want to view payments details of a selected student. Thus, I'd like to select some values (firstname,middlename,surname) from tblstudents and (all values from tblpayments WHERE class = @class -I know how to deal with @class, its a value in a textbox and present as a column in tblpayments) I want to INNER JOIN the tables with condition that (tblstudents.studentid = tblpayments.studentid).
The table tblpayments has columns (studentid,class and some contributions/payments names) I want to filter these contirbution/payments because some are valid for some classes only.
I have tried the following codes:
SQL = "SELECT ( SELECT tblstudents.FirstName,tblstudents.MiddleName,tblstudents.Surname, tblpayments.* " _ & "FROM tblstudents INNER JOIN tblpayments ON " _ & "tblpayments.studentid = tblstudents.studentid " _ & "WHERE tblpayments.class = @class AND tblpayments.studentid= @studentid"
but this did not deal with the first WHERE condition (tblpayments.class = @class), it brought results regardless of the @class.
I tried this code:
SQL = "SELECT (SELECT tblstudents.FirstName,tblstudents.MiddleName,tblstudents.Surname " _ & "FROM tblstudents WHERE tblpayments.studentid= @studentid) " _ & "(SELECT tblpayments.* " _ & "WHERE tblpayments.class = @class)"
but VB said there was an error with the code.
I tried this solution from Daniweb to no avail.
I will appreciate any help please.