VB.NET MYSQL DATATABLE

Pauls Pauls

am new to vb.net and trying to do some reporting but while trying to retrieve records i got problems in the format of the report. I have the following tables in the database: 1. Exams ExamType ExamId EXAM I 1 EXAM II 2 EXAM III 3 2. SUBJECTS SUBJECTID SUBJECTNAME 1 ENGLISH 2 GEOGRAPHY 3 MATHEMATICS 4 BIOLOGY 5 CHEMISTRY 6 PHYSICS 3. MARKS STUDENTNO TOTALSCORE ENGLISH GEOGRAPHY MATHEMATICS BIOLOGY C. 20 240 50 60 70 60 21 23 34 56 ... now i wish to retrieve records in the format: SUBJECTNAME Exam I Exam II Exam III Avg ENGLISH 50 78 67 GEOGRAPHY 60 67 MATHEMATICS 70 56 BIOLOGY 60 90 CHEMISTRY - PHYSICS - Any idea on i can go about this?. any help will be appreciated

Peter Leow

First thing first, redesign the marks table as follows:

studentno subjectid mark
20 1 50
20 2 60
20 3 70
20 4 60
21 1 70
... ... ...

The subjectid of the marks table will be the foreign key referencing the subjectid (primary key) of the subject table The SQL query to retrieve the desire marks by subject by student will be:

SELECT s.subjectname, m.mark FROM marks m inner join subjects s
on m.subjectid = s.subjectid WHERE studentno = 20

Find out more on http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]

Lost User

Unless you have omitted in your post something from your database-model, my first suggestion would be to rework your database-model a bit. You're missing some stuff there which would make a lot of sense:

Table ExamType
ExamTypeId (Primary Key)
ExamTypeName (Exam I / Exam II / Exam III)

Table Subject
SubjectId (Primary Key)
SubjectName (English / Geography / ...)

Table Student
StudentId (Primary Key)
StudentName

Table Exam
ExamId (Primary Key)
ExamTypeId (Foreign Key referencing ExamType.ExamTypeId)
SubjectId (Foreign Key referencing Subject.SubjectId)
ExamTakenDate

Table Mark
ExamId (Foreign Key referencing Exam.ExamId) \_ Primary Key
StudentId (Foreign Key referencing Student.StudentId) /
Score

Then you can get the marks with a query like this:

SELECT St.StudentName,
Sj.SubjectName,
Et.ExamTypeName,
Ex.ExamTakenDate,
Mk.Score
FROM Student AS St
JOIN Mark AS Mk ON St.StudentId = Mk.StudentId
JOIN Exam AS Ex ON Mk.ExamId = Ex.ExamId
JOIN ExamType AS Et ON Ex.ExamTypeId = Et.ExamTypeId
JOIN Subject AS Sj ON Ex.SubjectId = Sj.SubjectId
WHERE
StudentId = ... /* however you want to narrow your query result */
ExamId = ...
SubjectId = ...

Edit: What you can read when following the link Peter Loew has posted in his answer is basically the reasoning for my suggested rework of your database-model.

Recursion: see Recursion.

Pauls Pauls

thanks for replying...i have tried and it assists. But sorry for this. I forgot something in the final report form SUBJECTNAME Exam I Exam II Exam III Avg ENGLISH 50 78 67 GEOGRAPHY 60 67 MATHEMATICS 70 56 BIOLOGY 60 90 CHEMISTRY - PHYSICS - Kindly assist...Will be grateful