I have a table of Users:
|Username|UserType|Points| |John |A |250 | |Mary |A |150 | |Anna |B |600 |
|UserType|MinPoints|Level | |A |100 |Bronze | |A |200 |Silver | |A |300 |Gold | |B |500 |Bronze |
And I'm looking for a query to get the level for each user. Something along the lines of:
SELECT * FROM Users U INNER JOIN ( SELECT TOP 1 Level, U.UserName FROM Levels L WHERE L.MinPoints < U.Points ORDER BY MinPoints DESC ) UL ON U.Username = UL.Username
Such that the results would be:
|Username|UserType|Points|Level | |John |A |250 |Silver | |Mary |A |150 |Bronze | |Anna |B |600 |Bronze |
Does anyone have any ideas or suggestions on how could I do this without resorting to cursors?