Help managing many-to-one results for lookup

I didn't see this exactly asked, so I'm hoping it wasn't.

I have a table that has multiple columns with code variables and a table that has all lookup codes and de开发者_StackOverflow中文版scriptions for the whole database. Is there a way to join the lookup values so that everything stays on one row, instead of what i'm getting where one row has the race value and one row has the sex value. Thanks. I'm using TOAD but understand SQL.

Table 1                      
User_id    Race_cd  Sex_cd              
101          3201     4501              
102          3201     4502              
103          3202     4501               
104          3203     4501 


Table 2
CD_Num      CD_descrip
3201        White
3202        Black
3203        Asian
4501        Male
4502        Female

I played around for an hour with the joins over your tables, without an easy result.

Then I created views like this :

create view race as select * from lookup where id < 4000

create view sex as select * from lookup where id > 4000

thenafter, the select was just this easy :

select user.id, race.desc, sex.desc  from users, race, sex 
       where user.ra = race.id 
       and user.se = sex.id

showing up this :

101    White    Male
102    White    Female
103    Black    Male
104    Asian    Male

May this inspire you a nice solution ! ( You will naturally have to deal with the "between value and value" predicate when creating your views. )