Hi all!
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.
Thanks.