SELECT some values from one table and some from another table (with WHERE)?

[savedlema]

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.

[Reverend Jim]

It would help to see the code you are using to add in the parameters in case there is a problem there, but for now try

 SQL = "SELECT s.FirstName, s.MiddleName, s.Surname, p.* " _
     & "  FROM tblstudents AS s, tblpayments as p " _
     & " WHERE s.studentid = p.studentid " _
     & "   AND p.class = @class"

The p and s are temporary aliases. They make for a lot less typing and a lot fewer typos.

 SQL = "SELECT s.FirstName, s.MiddleName, s.Surname, p.* " _
     & "  FROM tblstudents AS s INNER JOIN tblpayments as p " _
     & "    ON s.studentid = p.studentid " _
     & "   AND p.class = @class"

is functionally the same query

[savedlema]

Thanks Reverend!

For parameters, I use the following (I learnt this from you!-I appreciate):

        cmd.Parameters.AddWithValue("@studentid", txtAccountId.Text)
        cmd.Parameters.AddWithValue("@class", txtClass.Text)

The error msg I have been getting (with my first code) is "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

I can't see the syntax error it says.

[savedlema]

Reverend,
Your code works, but, I've discoverd I need more than what I though. I'm trying to think if what I really want can be done: Is there a way I can instruct your code above that, instead of selecting p.* it should select everything in p but with the condition (SELECT name FROM tblcontributions WHERE form1 ='yes').
To make it clear: the table payments contains details of students paying their contributions. All school contributions are also columns in this table. Now, some contributions are only valid for some classes, so, when I want to to view students's account/made payments, I don't want the column for a contribution that's not valid for him to be shown.I have another table (see my inline code) that can filter contributions by looking if a contribution has a 'Yes' or 'No' for a given class.

-I understand my case may be confusing. Sorry.

[Reverend Jim]

You can't select some columns for some records and not for others. All records have to have the same columns. You'll have to post the exact structure of the three tables for me to be more specific. An example of the output you want to see would also be a big help.

[savedlema]

The structure:
The system I'm creating is for managing students contributions payments. The tables I think are concerned here:

1.tblContributions:(name,amount,form1,form2,form3,form4):this keeps names of contributions,amount and specify which class (form1....)is eligible for this,Yes for eligible and No for not eligible.

2.tlbpayments (studentid,class, [all contributions names in tblcontributions]:Payments made are stored here.
3.tblstudents (studentid,firstname,middlename,surname,class...):keeps information about a student.

I'm done with receiving payments from students and recording them. Now I want to view an individual student account to see if he has any oustanding debts to pay. I've been able to search that student (with student Id) and put his names and class in textboxes on a form. Then on the same form, I want to pull out his payment records (from tblpayments) by searching for his studentid, and using his class(in the textbox) as a search criterion.

I would like to the following results:
Studentid,firstname,midlename,surname, (and only those contributions which has a 'Yes' for this student's class (in the tblcontributis) the reason for this is, there is no point in indicating that a form1 student has not paid a fee for Form2 examination, while he is not supposed to pay it)

I'm trying to attach screenshots of the tables with this (I hope they shows up). Did I make it clear?
I see this is long.
Thanks.

[Reverend Jim]

You can select specific columns (by listing the column names) and you can select specific records (by using a WHERE clause) but you can't vary the columns within a query. You might want to consider redoing your tables. I don't understand your column names. For example, I would have thought a class would be something like "History" or "Chemistry". Instead you have form1, form2, etc. And I have no idea about Cooker or MalipoAda. tblStudents should contain static information about a student such as name, birthdate, address, phone. Each record would have a unique studentID which would be used in all other tables. A separate table would contain class info with one record per student per class. tblContributions needs further explanation. I have no idea what this is.

[savedlema]

Reverend, in my country, we call high school classes like "form1,form2..." its like 'class1,class2, or 'grade1','grade2' e.t.c. And, "Cooker","MalipoAda (in our local language) are the contributions/payments students are required to pay. (which are added,edited,deleted in the tblcontributions (and everywhere they appear)).
But, I'm considering redoing my tables as you suggested.

Thanks so far.