combining tables while including NULLs

[dbaJSmith]

Using SQL Server 2000

I'm trying to perform a series of joins to get multiple values out of a single set of tables. The purpose of this is to get a single view of all responses to a single responseID. The data is stored as:

ResponseAnswer
ResponseID[tab]ItemID[tab]OptionID
1         [tab]500    [tab]10
1         [tab]501    [tab]25
2         [tab]500    [tab]11
2         [tab]501    [tab]24
3         [tab]501    [tab]23
4         [tab]500    [tab]10

ItemOptions
OptionID[tab]TextID
10      [tab]100
11      [tab]110
23      [tab]230
24      [tab]240
25      [tab]250

Text
TextID[tab]TextValue
100   [tab]'Y'
110   [tab]'N'
230   [tab]'3'
240   [tab]'4'
250   [tab]'5'

...and I want the following result:

ResponseID[tab]responseQ1[tab]responseQ2[tab]...[tab]ResponseQn
1         [tab]Y         [tab]5         [tab]...[tab]value
2         [tab]N         [tab]4         [tab]...[tab]value
3         [tab]NULL      [tab]3         [tab]...[tab]value
4         [tab]Y         [tab]NULL      [tab]...[tab]value
...

I can't seem to get this right; I'm trying to leave the NULLs in place, but the problem is that the ResponseAnswers table doesn't store a NULL response for that question. Using the query I wrote, the only rows that would return would be 1 and 2, since none of the values can be NULL:

select response.responseID, response.Ended as responseDate, 'I' as responseType, LText.TextValue as responseLoc, 
Text_1.TextValue as responseSvcDate, Text_2.TextValue as responsePC, Text_3.TextValue as responseQ1, 
Text_4.TextValue as responseQ2, Text_5.TextValue as responseQ3
from response 
left outer join responseAnswers responseAnswers on response.responseID = responseAnswers.responseID 
left outer join ItemOptions ItemOptions on responseAnswers.optionID = ItemOptions.optionID 
left outer join Text LText on ItemOptions.TextID = LText.TextID 
left outer join responseAnswers responseAnswers_1 on response.responseID = responseAnswers_1.responseID 
left outer join ItemOptions ItemOptions_1 on responseAnswers_1.optionID = ItemOptions_1.optionID 
left outer join Text Text_1 on ItemOptions_1.TextID = Text_1.TextID 
left outer join responseAnswers responseAnswers_2 on response.responseID = responseAnswers_2.responseID 
left outer join ItemOptions ItemOptions_2 on responseAnswers_2.optionID = ItemOptions_2.optionID 
left outer join Text Text_2 on ItemOptions_2.TextID = Text_2.TextID 
left outer join responseAnswers responseAnswers_3 on response.responseID = responseAnswers_3.responseID 
left outer join ItemOptions ItemOptions_3 on responseAnswers_3.optionID = ItemOptions_3.optionID 
left outer join Text Text_3 on ItemOptions_3.TextID = Text_3.TextID 
left outer join responseAnswers responseAnswers_4 on response.responseID = responseAnswers_4.responseID 
left outer join ItemOptions ItemOptions_4 on responseAnswers_4.optionID = ItemOptions_4.optionID 
left outer join Text Text_4 on ItemOptions_4.TextID = Text_4.TextID 
left outer join responseAnswers responseAnswers_5 on response.responseID = responseAnswers_5.responseID 
left outer join ItemOptions ItemOptions_5 on responseAnswers_5.optionID = ItemOptions_5.optionID 
where (responseAnswers.itemID = 22839) and (responseAnswers_1.itemID = 22832) 
and (responseAnswers_2.itemID = 22838) and (responseAnswers_3.itemID = 22840) 
and (responseAnswers_4.itemID = 22837) and (responseAnswers_5.itemID = 22836)

Any ideas? Help! Thanks!

 

[ESquared]

Idea: do a search on the word PIVOT.

 

[gmmastros]

You probably know how to make a Left Join behave like an inner join. Right?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dbaJSmith]

gmmastros - I've run this under both inner joins and left outer joins; still the same issues.

ESquared - I'll investigate that option, thanks.

 

[dbaJSmith]

ESquared - can you clarify on how you think a pivot will help this situation? I looked it over, and while pivots are more complex in SQL Server 2000 than 2005, they can be done. I guess I'm just having a hard time seeing how that will arrange the data; I'm not trying to apply any summary to the results, I just want a flat table view.

 

[gmmastros]

Someone edited my first response. I don't appreciate that.

dba guy.... try this:

select response.responseID, 
       response.Ended as responseDate, 
       'I' as responseType, 
       LText.TextValue as responseLoc,
       Text_1.TextValue as responseSvcDate, 
       Text_2.TextValue as responsePC, 
       Text_3.TextValue as responseQ1,
       Text_4.TextValue as responseQ2, 
       Text_5.TextValue as responseQ3
from   response
       left outer join responseAnswers responseAnswers 
          on  response.responseID = responseAnswers.responseID
          And (responseAnswers.itemID = 22839)
       left outer join ItemOptions ItemOptions 
          on  responseAnswers.optionID = ItemOptions.optionID
       left outer join Text LText 
          on ItemOptions.TextID = LText.TextID
       left outer join responseAnswers responseAnswers_1 
          on  response.responseID = responseAnswers_1.responseID
          and responseAnswers_1.itemID = 22832
       left outer join ItemOptions ItemOptions_1 
          on responseAnswers_1.optionID = ItemOptions_1.optionID
       left outer join Text Text_1 
          on ItemOptions_1.TextID = Text_1.TextID
       left outer join responseAnswers responseAnswers_2 
          on  response.responseID = responseAnswers_2.responseID
          and responseAnswers_2.itemID = 22838
       left outer join ItemOptions ItemOptions_2 
          on responseAnswers_2.optionID = ItemOptions_2.optionID
       left outer join Text Text_2 
          on ItemOptions_2.TextID = Text_2.TextID
       left outer join responseAnswers responseAnswers_3 
          on  response.responseID = responseAnswers_3.responseID
          And responseAnswers_3.itemID = 22840
       left outer join ItemOptions ItemOptions_3 
          on responseAnswers_3.optionID = ItemOptions_3.optionID
       left outer join Text Text_3 
          on ItemOptions_3.TextID = Text_3.TextID
       left outer join responseAnswers responseAnswers_4 
          on  response.responseID = responseAnswers_4.responseID
          And responseAnswers_4.itemID = 22837
       left outer join ItemOptions ItemOptions_4 
          on responseAnswers_4.optionID = ItemOptions_4.optionID
       left outer join Text Text_4 
          on ItemOptions_4.TextID = Text_4.TextID
       left outer join responseAnswers responseAnswers_5 
          on response.responseID = responseAnswers_5.responseID
          And responseAnswers_5.itemID = 22836
       left outer join ItemOptions ItemOptions_5 
          on responseAnswers_5.optionID = ItemOptions_5.optionID

If this returns the correct data and you would like me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dbaJSmith]

Ahh - I see what you meant now. Tested it; it brings up the data in the format I need, although it brings up far too much data (there are other sets of questions than this one). A simple filter on the question set should do the trick, though. Thanks for your patience!

 

[ESquared]

I think you'll find that this is easier to maintain and performs substantially better than joining for each column.

SELECT
   ResponseID,
   ResponseDate = Response.Ended
   ResponseType = 'I',
   responseLoc = Max(CASE WHEN A.ItemID = 22839 THEN T.TextValue ELSE NULL END),
   responseSvcDate = Max(CASE WHEN A.ItemID = 22832 THEN T.TextValue ELSE NULL END),
   responsePC = Max(CASE WHEN A.ItemID = 22838 THEN T.TextValue ELSE NULL END),
   responseQ1 = Max(CASE WHEN A.ItemID = 22840 THEN T.TextValue ELSE NULL END),
   responseQ2 = Max(CASE WHEN A.ItemID = 22837 THEN T.TextValue ELSE NULL END),
   responseQ3 = Max(CASE WHEN A.ItemID = 22836 THEN T.TextValue ELSE NULL END)
FROM
   Response R
   LEFT JOIN ResponseAnswers A ON R.ResponseID = A.ResponseID AND A.ItemID IN (22839, 22832, 22838, 22840, 22837, 22836)
   LEFT JOIN ItemOptions O ON A.OptionID = O.OptionID
   LEFT JOIN Text T ON O.TextID = T.TextID
GROUP BY
   ResponseID,
   Response.Ended

The A.ItemID IN (<List>) part isn't actually necessary for correct results, but if the selection of answers is very much shorter than all given ResponseAnswers, it is necessary for query performance. If only one or two ResponseAnswers is omitted, then leaving this out may improve performance. Experimentation is in order.

You should also test whether this performs better:

FROM
   Response R
   LEFT JOIN (
      ResponseAnswers A
      INNER JOIN ItemOptions O ON A.OptionID = O.OptionID
         AND A.ItemID IN (22839, 22832, 22838, 22840, 22837, 22836) -- can't be in the where clause, has to be on the join
      INNER JOIN Text T ON O.TextID = T.TextID
   ) ON R.ResponseID = A.ResponseID

 

[ESquared]

P.S. A little more extended searching on PIVOT would have found examples like this one. This is a classic PIVOT strategy. It doesn't matter that you only want single values and aren't getting the Max of anything. The Max function is used only to throw away the NULL values that you don't want for all the rows that don't apply to the current contextual/grouped-by value(s).

It may be instructive for you to remove the GROUP BY and the Max, leaving the expression inside of the Max in place, and run the query. Any time you can produce staggered results like this where all the holes you don't want can be filled with NULLs, you can use this PIVOT strategy to yield single rows per grouped-by item.

 

[ESquared]

P.P.S. If you're displaying this in a web page, don't use the PIVOT. Just return two rowsets, one with the first few columns as columns (the group by values) and one with the remaining columns as rows (the pivoted values). If they are sorted correctly (the query for both rowsets can include extra tables in order to apply the same order-by clause), then you can simply step through them together. After putting out a value, do a .MoveNext. If the next value present in the 2nd rowset isn't for the current column/row, put out a blank and move to the next column/row without doing another MoveNext.

You might find it instructive to look up how a MERGE JOIN works in SQL Server. You would be emulating this in your web page.

More easy than this is simply to return a joined rowset where the grouped-by columns are repeated over and over. Some careful programming can achieve the same result. I don't like this method because of the duplication of data going over the network, and it will perform worse (I expect). But it's still viable if the other way I described doesn't seem to click for you.