Lookup tables Usage

use Test

go

drop table Lkup

go

create table Lkup

(

custname varchar(20),

Salary varchar(20),

Gender Char(2),

Age varchar(10),

segment_ID varchar(50)

)

select * from Lkup

insert into Lkup values ('Faizal','large','Y','Adult',' 1 YEAR')

insert into Lkup values ('Faizal','small','Y','Minor',' 2 YEARS')

insert into Lkup values ('kumar','large','Y','Adult','1 YEAR')

insert into Lkup values ('kumar','small','Y','Minor','2 YEARS')

insert into Lkup values ('Faizal','large','Y','',' 6 Month')

insert into Lkup values ('kumar','large','Y','','6 Month')

drop table C_Details

go

create table C_Details

(

C_name varchar(10),

salary varchar(10),

Gender varchar(5),

Age varchar(10)

)

insert into C_Details values ('','5000','','10')

insert into C_Details values ('Kumar','','','10')

insert into C_Details values ('Faizal','5000','M','24')

insert into C_Details values ('Kumar','5000','M','28')

insert into C_Details values ('Faizal','4000','M','14')

insert into C_Details values ('Kumar','4000','M','17')

drop table #C_Details

select C.C_name,

"salary"=

case

when c.salary=5000 then 'Large'

when C.salary=4000 then 'small'

End

,

"Age"=

case

when C.Age>18 then 'Adult'

else 'Minor'

End

,"Gender"=

case when C.gender='M' then 'Y'

else 'N'

End

into #C_Details

 from C_Details as C

--inner join Lkup as L

--on C.Age=L.Age

select * from #C_Details as C

Left outer join Lkup as CD

on C.Age=CD.Age

and

C.salary=Cd.salary

and C.C_name=CD.custname

and C.Gender=CD.Gender