Filtering results based on many to many relationships

Question

I'd like to filter the results of a query which returns the results from two tables which have any to many relationship to each other. Consider the following scenario: (SQL Server or MS Access)
Table: Students
--StudentID
--StudentName

Table: Grades
--StudentID (Foreign Key)
--CourseID (Foreign Key. Ignore for the sake of simplicity)
--StudentGrade (out of 100)

Now I'd like to filter the results and return only the grade details of students whose grades are all > 50. Right now, I'm taking the following steps:
1- Get all the students and grades:

DataTable dtAll = GetData("SELECT * FROM Students INNER JOIN Grades ON Students.StudentID = Grades.StudentID)  

2- Get all the students who have at least one grade < 50. (Failed students)

    DataTable dtFailed = GetData("SELECT Distinct(Students.StudentID), Grades.StudentGrade FROM Students INNER JOIN INNER JOIN Grades ON Students.StudentID = Grades.StudentID WHERE Grades.StudentGrade < 50")  

3- Remove all the fields related to failed students:

foreach (DataRow failedRow in dtFailed.Rows)
        {
            int counter = 0;
            while (counter < dtAll.Rows.Count)
            {
                if (counter >= dtAll.Rows.Count)
                    break;
                curRow = dtAll.Rows[counter];

                if (Convert.ToInt32(curRow["StudentID"]) == Convert.ToInt32(failedRow["StudentlID"]))
                    dtAll.Rows.Remove(curRow);
                else
                    counter += 1;
            }
        }  

This chunk of code simply iterates through all dtFailed rows and removes the rows in dtAll which have an entry in dtFailed. (All students' grades will be removed if the student have at least one failed course.) I suppose this is a common scenario in production environment and hopefully there's a faster and cleaner way for doing this. Appreciate if anyone shares it with me.
P.S. If this can only be done with stored procedures, or any SQL specific way (Not access) please share it since I can use SQL Server instead of MS Access if needed.

Answer 1

SELECT s.StudentId  ,
       s.StudentName,
       g.StudentGrade
FROM   Students s
       JOIN Grades g
       ON     g.StudentID = s.StudentID
WHERE  g.StudentGrade     > 50
AND    NOT EXISTS
       (SELECT *
       FROM    Grades g2
       WHERE   g2.StudentGrade <= 50
       AND     g.StudentID   = g2.StudentID)

Answer 2

Perhaps use LINQ to query your dtAll instead of hitting the database again?

//student IDs having min grade greater than 50
 var studentIDs = from f in table.AsEnumerable()
                         group f by new { StuID = f.Field<int>("StudentID"), 
                                       StuGrade = f.Field<int>("StudentGrade") } 
                         into g
                         where g.Min(x => x.Field<int>("StudentGrade") > 50)
                         select new
                         {
                             StudentID = g.Key.StuID,
                             StudentGrade = g.Key.StuGrade
                         };


foreach (var item in studentIDs)
{
    Console.WriteLine(item.StudentID + " : " + item.StudentGrade);
}