I have a database where I need to query to get records from one table and then lookup on another table to see if that value exists there. That table might return multiple records and I want the one with the most recent date.
So table 1 is basically:
ID (Primary Key)
Name
Test_Score
And Table 2 is
Test_Id (Primary)
Student_ID (Foreign Key)
Test_Score
Test_Date
As I'm going through the records, if the no tests exist in table 2 for the Student_id, I want to use the score in table 1, otherwise I want to use the score from table 2 with the most recent date. I have this all working in C# code, but the clients want it in a stored procedure for reporting purposes and I'm seeing some performance issues since the tables are quite large. Also, this basic example actually happens multiple times against multiple tables.
I'm sure there is an elegant way of doing this that is fast and efficient, but I can't seem to come up with anything but using a cursor.
Does someone know the straight forward solution?