SQL - Criteria Issue

Hi all,

I'm new to these forums, so I hope this post has gone in the right place. I appologise if it has not.

I have an SQL problem that I was hoping you'd able to help with...

I have two tables, one with a Student's Mark in it and another with Grade Boundaries in them:

Student Marks:
(Student ID | Mark)
(4 | 100)
(5 | 80)
(6 | 79)
(7 | 69)
(8 | 59)


Grade Boundaries:
(Grade | Lower Boundary | Upper Boundary)
(A | 80 | 100)
(B | 70 | 79)
(C | 60 | 69)
(D | 50 | 59)
(U | 0 | 49)


I need an SQL statement which will return (Student ID | Grade).

Now, so far I have this:
SELECT *
FROM [Student Marks], [Grade Boundaries]
WHERE [Lower Boundary])<=[Mark] AND [Upper Boundary]>=[Mark];

This works fine, but I was wondering would I be able to perform the same calculation if I removed the Lower Boundary Field? If so, how would it be performed?

Basically, Grade := Lower <= Mark <= Upper, but Lower and Upper are related, so do I really need to store both??

Any help would be much appreciated.

Thanks for your time.

Regards,
Louis Rose.

You structure allows for a person to have one mark but two different grades. How do you assign the correct grade?

Ooops that was a typo. I'll edit the original post.

Thanks!

Did you still have a problem? How are you joining the tables?

The problem still exists as stated in my original post. I've just changed a couple of the Grade Boundary numbers.

I'm not joining the tables at all.

SELECT * FROM student_marks
CROSS JOIN grade_boundaries
HAVING student_marks.id BETWEEN grade_boundries.lower AND grade_boundries.upper

Don't know if it'll work, but I think the idea is good.

Jabol, I've not tried you suggestion yet but it looks good.

The nub of the problem is removing a field (either lower boundary or upper boundary) though, as:

Upper Boundary(x) = Lower Bounary(x-1) + 1

where x is the Grade.

Well, if you don't store both bounds, then you must join the grade_boundary table TWICE in order to get the boundary. If my mark is 76 with only a high-boundary I can tell what grades it is NOT (B or A), but I don't know if it is a C or not until I look at where the C mark cutoff is.

You could accomplish this 'one boundary' method with some bastardization of SQL -- you could do an ORDER BY boundary (acending or decending depends on where you put your boundaries) and take the first row that pops up.

What you have now is fine, why do you want to change it?

I want to change it as the end user does not want to have to change both A's lower boundary and B's upper boundary when the grade boundary works.

Could you give me an example in SQL to try please? Thanks.

Regards,
Louis.