Retrieve a field from a table or another with condition

• Corobori

  Ten Centuries

  Points: 1316

  More actions

  September 15, 2005 at 10:49 am

  #102799

  Because field is "multiuse" I need to retrieve a value depending on a column

  CREATE TABLE [dbo].[#tblAds] (

  [Ad_Id] [int] NOT NULL ,

  [Ad_Desc] [nvarchar] (50),

  [Ad_Keyword_Id] [int] NOT NULL ,

  [Ad_Switch_Id] [int] NOT NULL

  ) ON [PRIMARY]

  GO

  insert into #tblAds VALUES(1,'Ad1',1,1)

  insert into #tblAds VALUES(2,'Ad2',1,2)

  insert into #tblAds VALUES(3,'Ad3',2,3)

  CREATE TABLE [dbo].[#tblCategory] (

  [Ca_Id] [int] NOT NULL ,

  [Ca_Desc] [nvarchar] (50)

  ) ON [PRIMARY]

  GO

  insert into #tblCategory VALUES(1,'Category1')

  insert into #tblCategory VALUES(2,'Category2')

  CREATE TABLE [dbo].[#tblType] (

  [Ty_Id] [int] NOT NULL ,

  [Ty_Desc] [nvarchar] (50)

  ) ON [PRIMARY]

  GO

  insert into #tblType VALUES(1,'Type1')

  insert into #tblType VALUES(2,'Type2')

  If Ad_Switch_Id=1 I would like to retrieve the Description field from tblCategory

  If Ad_Switch_Id<>1 I would like to retrieve the Description field from tblType

  So the result should be

  1, 'Ad1', 'Category1'

  2, 'Ad2', 'Type1'

  3, 'Ad3', 'Type2'

  
  Jean-Luc
  www.corobori.com
  

• Adrienne250

  Right there with Babe

  Points: 737

  More actions

  September 15, 2005 at 11:02 am

  #590246

  Will this work:

  select

   Ad_Desc,

   Descr = case when Ad_Switch_Id = 1 then Ca_Desc else Ty_Desc end

  from #tblAds

  left join #tblCategory on Ad_Keyword_Id = Ca_Id

  left join #tblType on Ad_Keyword_Id = Ty_Id