Without while loop

• ningaraju.n

  SSCrazy

  Points: 2322

  More actions

  November 16, 2010 at 8:55 pm

  #226763

  /*Table describes the question details*/

  declare @DetailTable table

  (

  Idbigint identity(1,1),

  SelectionId bigint,

  FilterId1bigint,

  FilterId2bigint,

  FilterId3bigint

  )

  /*Table contains the question with details*/

  declare @QuestionTable table

  (

  QuestionIdbigint,

  QuestionLabelnvarchar(100),

  FilterId1bigint,

  FilterId2bigint,

  FilterId3bigint

  )

  insert into @DetailTable(SelectionId,FilterId1,FilterId2,FilterId3)

  select 1,1,2,1

  union all

  select 1,2,4,3

  union all

  select 1,3,7,2

  union all

  select 1,1,9,6

  insert into @QuestionTable (QuestionId,QuestionLabel,FilterId1,FilterId2,FilterId3)

  select 1,'Q01',1,9,6

  union all

  select 2,'Q02',1,9,6

  union all

  select 3,'Q03',1,9,7

  union all

  select 4,'Q04',3,7,2

  union all

  select 5,'Q05',3,7,2

  union all

  select 6,'Q06',3,7,2

  union all

  select 7,'Q07',3,7,2

  union all

  select 8,'Q08',2,4,3

  union all

  select 9,'Q09',2,4,3

  union all

  select 10,'Q010',1,2,1

  union all

  select 11,'Q011',1,2,1

  union all

  select 12,'Q012',1,2,1

  union all

  select 13,'Q013',1,9,2

  union all

  select 14,'Q014',1,4,6

  /*

  Now i need to have a query without using while loop/cursor to fetch the question which are defined in the @DetailTable table

  so output of the query should be questionids and questionlabel

  */

  /*

  1,'Q01'

  2,'Q02'

  4,'Q04'

  5,'Q05'

  6,'Q06'

  7,'Q07'

  8,'Q08'

  9,'Q09'

  10,'Q010'

  11,'Q011'

  12,'Q012'

  the QuestionIds 3,13,14 are discraded in result set as they doesn't match the definition in @DetailTable table

  Thanks in advance

  */

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  November 16, 2010 at 9:46 pm

  #1251675

  Not sure but i hope this is what you want..

  SELECTQT.QuestionId, QT.QuestionLabel

  FROM@QuestionTable QT

  WHEREEXISTS

  (

  SELECT*

  FROM@DetailTable DT

  WHEREQT.FilterId1 = DT.FilterId1

  ANDQT.FilterId2 = DT.FilterId2

  ANDQT.FilterId3 = DT.FilterId3

  )

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 16, 2010 at 9:47 pm

  #1251676

  Really should ask to see what you have tried as this is a relatively easy solution.

  select

  qt.QuestionId,

  qt.QuestionLabel

  from

  @QuestionTable qt

  inner join @DetailTable dt

  on (qt.FilterId1 = dt.FilterId1 and

  qt.FilterId2 = dt.FilterId2 and

  qt.FilterId3 = dt.FilterId3);

• ningaraju.n

  SSCrazy

  Points: 2322

  More actions

  November 16, 2010 at 9:52 pm

  #1251677

  Thanks Guys