SQL Query Doubt

Hello:

I had a Sql query doubt. I have two tables. One is table with profile data but in this table instead of actually values it stores codes. The value of each code is stored in another table. Something like this

Profile Table

ProfileID    Title  First Name  Last Name       State

1             122          xxx          yyy                 333

So as you can see title and state both are code which are further stored in another table which is something like this

Code_ID         Code_Value

122                   Director

124                   International

333                  AZ

Now I want to replace all these codes with values. Something like this

ProfileID    Title               First Name  Last Name       State

1             Director           xxx              yyy                 AZ

I tried Case statement but it fails. I am not sure how to do this. I have hundred thousands of records to doing programmaticall slows the process. I would appreciate your help or ideas.