Lookup results in another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Lookup results in another table

Author

Topic

hcmj99
Starting Member

2 Posts

Posted - 2013-07-11 : 04:55:07

Hi,
I have a table "Ratings" in which I store rating categories like the following:
FROM TO Rating
1 10 1
11 20 2
21 30 3

In another table "Results" I have the actual results like:
Employee ID Results Rating
120 6 ?
130 18 ?

Now I would like calculate the rating in the results table in the rating column (where "?" in the above example)

How can I do that with an SQL update command - maybe using a Case statement - but I don't know how to do that by looking up values from another table

Please advise...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 05:05:19

[code]
SELECT r.EmployeeID,r.Results,rt.Rating
FROM Results r
INNER JOIN Ratings rt
WHERE r.Results BETWEEN rt.[FROM] AND rt.[TO]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-11 : 05:11:26

quote:
Originally posted by visakh16

SELECT r.EmployeeID,r.Results,rt.Rating
FROM Results r
INNER JOIN Ratings rt
WHERE r.Results BETWEEN rt.[FROM] AND rt.[TO]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

OP needs a update statement!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 05:22:38

Make it into an update


UPDATE r
SET r.Rating=rt.Rating
FROM Results r
INNER JOIN Ratings rt
WHERE r.Results BETWEEN rt.[FROM] AND rt.[TO]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

hcmj99
Starting Member

2 Posts

Posted - 2013-07-11 : 09:39:35

It works.. thanks :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 11:15:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources