how can i get Students Grades using Grade,studets tables

Question

how can i get students grades using these 2 tables?

CREATE TABLE [dbo].[Grade](
 [ID] [int] NULL,
 [From] [int] NULL,
 [To] [int] NULL,
 [Grade] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Students]( [ID] [int] NULL, [Name] nvarchar NULL, [Score] [int] NULL ) ON [PRIMARY]

Answer 1

Ok, lets take a guess that the Students table has a column (probably an identity column) called 'ID' which is the students' unique identifier. Let's also assume that the Grade.ID field is a foreign key reference to the Student table (I know it doesn't say that, but it's probably a good idea.)

In that case, try this (untested code):

SELECT Student.Name, Grade.Grade, Grade.From, Grade.To
FROM Student, Grade
WHERE Student.ID = Grade.ID
ORDER BY Student.ID, Grade.To

That ought to give you a list of students, with their grades in ID order. It will repeat the student name for each grade record however (assuming no null values, of course.)

If you want one student instance with a list of grades, that's significantly harder.

(Btw, the more and better information in the initial question, the better the answers you're likely to get.)

---

Ok - second shot, now I understand the table structure:

SELECT Students.Name,  (select Grades.GradeName from Grades where Students.Score <= Grades.ScoreTo AND Students.Score > Grades.ScoreFrom) AS Grade
FROM Students

I changed the names a little to make them more obvious and distinct (having the same name for a table and a field on that table is confusing.)

This should give you the results you're looking for. BUT - you must ensure that your data is clean - if you have overlapping ranges, you will get a multiple sub-query return (which will fail.) You can overcome this with a 'SELECT TOP 1' restriction, but it's messy.