Help with a SELECT statement

• dt293

  Default port

  Points: 1412

  More actions

  June 16, 2009 at 5:47 am

  #155832

  IF OBJECT_ID('TempDB..#Question','U') IS NOT NULL DROP TABLE #Question

  IF OBJECT_ID('TempDB..#Answer','U') IS NOT NULL DROP TABLE #Answer

  CREATE TABLE #Question

  (

  Contact varchar(8),

  [LineNo] int,

  Question varchar(100)

  )

  CREATE TABLE #Answer

  (

  Contact varchar(8),

  [LineNo] int,

  Answer varchar(100)

  )

  INSERT INTO #Question

  (Contact,[LineNo],Question)

  SELECT 'C0000003', '10000','What would you like to study?' UNION ALL

  SELECT 'C0000003', '390000', 'When would you like to study?' UNION ALL

  SELECT 'C0000003', '495000', 'Do you like chips?' UNION ALL

  SELECT 'C0000004', '10000','What would you like to study?' UNION ALL

  SELECT 'C0000004', '390000', 'When would you like to study?' UNION ALL

  SELECT 'C0000004', '495000', 'Do you like chips?'

  INSERT INTO #Answer

  (Contact,[LineNo],Answer)

  SELECT 'C0000003', '120000', 'Course 1 Day' UNION ALL

  SELECT 'C0000003', '130000', 'Course 1 Evening' UNION ALL

  SELECT 'C0000003', '140000', 'Course 2 Day' UNION ALL

  SELECT 'C0000003', '150000', 'Course 3 Evening' UNION ALL

  SELECT 'C0000003', '160000', 'Course 4 Weekend' UNION ALL

  SELECT 'C0000003', '170000', 'Course 5 Weekend' UNION ALL

  SELECT 'C0000003', '430000', 'March' UNION ALL

  SELECT 'C0000003', '440000', 'April' UNION ALL

  SELECT 'C0000003', '450000', 'May' UNION ALL

  SELECT 'C0000003', '500000', 'Yes' UNION ALL

  SELECT 'C0000004', '120000', 'Course 1 Day' UNION ALL

  SELECT 'C0000004', '130000', 'Course 2 Evening' UNION ALL

  SELECT 'C0000004', '410000', 'Jan' UNION ALL

  SELECT 'C0000004', '420000', 'Feb' UNION ALL

  SELECT 'C0000004', '500000', 'Yes'

  SELECT * FROM #Question

  SELECT * FROM #Answer

  OK, I am going to try and explain this as best I can....

  I have two tables (Question & Answer) I need to bring back the question and answers for each contact, each contact can have multiple answers to the same question.

  If you look at the above code, the answers in the answer table relate to the question in the question table where the LineNo is less than their value.

  For example the first six rows for contact 'C0000003' has LineNo's from 120000 to 170000 which all relate to the same question - LineNo 10000 in the Question table.

  I hope this makes sense! Please ask if anything requires clarification.

  Any help is much appreciated.

  Thanks

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 16, 2009 at 6:10 am

  #1010111

  It's not really clear how LineNo joins, but this gives the correct result.

  WITH Questions(Contact,[LineNo],LineNoMax,Question) AS (

  SELECT a.Contact,a.[LineNo],MIN(b.[LineNo])-1,a.Question

  FROM #Question a

  LEFT OUTER JOIN #Question b ON b.Contact=a.Contact AND b.[LineNo]>a.[LineNo]

  GROUP BY a.Contact,a.[LineNo],a.Question)

  SELECT a.Contact,a.Question,b.Answer

  FROM Questions a

  INNER JOIN #Answer b ON b.Contact=a.Contact AND b.[LineNo] BETWEEN a.[LineNo] AND COALESCE(a.LineNoMax,b.[LineNo])

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• dt293

  Default port

  Points: 1412

  More actions

  June 16, 2009 at 8:55 am

  #1010247

  Perfect, thanks Mark.