hi..please help.. i have 2 tables tblStudents and tblGrades.

in tblStudents, all details of students and in tblGrades, grades of the students.
column "StudId" is their only common column..

in tblStudents:
StudId
LastName
FirstName

in tblGrades: columns are:
StudId
SubjectCode
Grade

what i want to achieve is to query on both tables with the below output:

StudId Name SubjectCode1 SubjectCode2 SubjectCode3
1 Test, Test 95 97 98

SubjectCodes are fixed.the Data will just depend on tblGrades..if StudId 1 doesnt have record for SubjectCode2, 0 will appear on the column for SubjectCode2

thanks..

really need this.

:-(

Recommended Answers

All 2 Replies

Well, if you need only one record from the tblStudents table, and multiple records from the tblGrades table, I suggest you just perform two queries. The first query will retreive the student info from the tblStudents table. Then, with the studentId that was retrieved, the second query retrieves the student's grades from the tblGrades table. You will have to merge the arrays with PHP or whatever language you are using to display the results. Example:

<?php
$q = 'SELECT StudId,
        LastName,
        FirstName
    FROM tblStudents';

// Let's say the results of that query were stored in $students.
// We will first prepare a query to retrieve each student's grades.
// Guessing and hoping you are using for example PDO or mysqli, you will understand
// the following query (with the questionmark in it).
$q = 'SELECT SubjectCode,
        Grade
    FROM tblGrades
    WHERE StudId = ?
    ORDER BY SubjectCode ASC';

foreach($students as &$student)
{
    // The query is executed here. Let's say the results are stored in $grades.
    // We will add the grades to the current student now:
    $student['grades'] = $grades;
}

// The $students array now contains info about the students and their grades.
echo '<p>Students and grades:</p><pre>'; print_r($students); echo '</pre>';

i just want to create a stored procedure and call in asp.net vb. .
I managed to display what i want.thanks. .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.