From: esquel@sommarskog.se   
      
   tshad (tfs@dslextreme.com) writes:   
   > That does work as well.  Not sure why.  I will have to look closer at it.   
   > Especially since I am not quite sure how the cross join works here.   
      
   Actually, it is worth to take a look it. For this particular problem, the   
   query is overly complicated, but the technique as such comes to use at   
   times. That is, when you need to convert column to rows, the typical   
   technique is to cross join with a small table of constants and then use   
   these constants to control CASE expressions. (There is also the UNPIVOT   
   keyword, but I've learnt that syntax very well.)   
      
   >> 1) The way I written the LEFT JOINs is the "right" way of doing an   
   >>   inner join from a left-joined table.   
   >   
   > Not sure what you mean here.   
      
   When you have:   
      
      LEFT JOIN EmployeeAnswerMap EAM ON EAM.AnswerID = A.AnswersID   
      JOIN Employees E ON E.EmployeeID = EAM.EmployeeID   
      
   You are effectively transforming the outer join to an inner join, because   
   the join to Employees filters out rows where EAM.AnswerID is NULL. Your   
   remedy was to use a second LEFT JOIN. That works too in this case, but   
   there are situations where this give a different result. (Which may be   
   considered far-fetch corner cases.)   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)