Lookup table

• shobapond

  SSC Enthusiast

  Points: 185

  More actions

  August 16, 2007 at 1:46 am

  #180312

  Hi

  I have collect data into a table named TA which contains many fields e.g Fever, Vomiting, Diarrhea which can take the values of Yes/No/Unknown. I have created a lookup table containing 1=Yes 2=No 3=Unknown. I am trying to write a query to pull everything from my TA table to get a Yes/No/Unknown rather than 1/2/3.

  I tried a SELECT * FROM TA INNER JOIN LOOKUP ON TA.Fever = LOOKUP.Sn

  Any suggestions or help.

  Thank you in advance

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736211

  More actions

  August 16, 2007 at 7:04 am

  #727312

  You need to specify the fields here from the first table and join in the second table for each value.

  So

  select

  a.pkcol

  , b.val 'fever'

  , c.val 'vomiting'

  ...

  from TA a

  inner join Lookup b

  on a.fever = b.id

  inner join Lookup c

  on a.vomiting = c.id

  etc.

  Doesn't sound like a fun job.

  Personally if these values don't change, I'd use a case statement instead, or just store the values in the table.

  select

  a.pk

  , case when a.fever = 1 then 'Yes'

  when a.fever = 0 then 'No'

  else 'Unknown'

  end 'fever'

  ...

  from TA a

• shobapond

  SSC Enthusiast

  Points: 185

  More actions

  August 17, 2007 at 12:36 am

  #727549

  Thanks Steve

  I'll used the first option.

  Cheers