From: tfs@dslextreme.com   
      
   "Erland Sommarskog"  wrote in message   
   news:Xns9E1D1C1F1DFBYazorman@127.0.0.1...   
   > tshad (tfs@dslextreme.com) writes:   
   >> If I do this with the UNION, I get 8 records a expected (4 for an   
   >> employee and 4 for a customer).   
   >>...   
   >> If I do LEFT JOINS on the Employees table as well as the Customer table,   
   >> I get the same rows plus 2 more rows, one with a color that no one chose   
   >> (Blue) and one that only one employee chose and no customer chose   
   >> (Orange).   
   >   
   > But note that the two queries return the same information. The information   
   > is just twisted in different ways. You could even say that it returns   
   > more information, since the result implies a relation between people   
   > who gave the same answer. Which probably is not real information but a   
   > fallacy.   
   >   
   Actually, it is a fallacy since there is no relationship between people   
   other than that they both gave the same answer which is not what I would   
   want.   
      
   > To get the same result as from the UNION query, you need to unpivot the   
   > result:   
   >   
   > WITH CTE  AS(   
   >   SELECT CASE u.a WHEN 'E' THEN E.FirstName ELSE C.FirstName END AS   
   > FirstName,   
   >          CASE u.a WHEN 'E' THEN E.LastName ELSE C.LastName END AS   
   > LastName,   
   >          QuestionText, AnswerText,   
   >          CASE u.a WHEN 'E' THEN 'Employees' ELSE 'Customers' END AS Source   
   >   FROM Questions Q   
   >   JOIN Answers A ON A.QuestionID = Q.QuestionID   
   >   LEFT JOIN (EmployeeAnswerMap EAM   
   >             JOIN Employees E ON E.EmployeeID = EAM.EmployeeID)   
   >         ON EAM.AnswerID = A.AnswersID   
   >   LEFT JOIN (CustomerAnswerMap CAM   
   >             JOIN Customers C ON C.CustomerID = CAM.CustomerID)   
   >        ON CAM.AnswerID = A.AnswersID   
   >   CROSS JOIN (SELECT a = 'E' UNION ALL SELECT 'C') AS u   
   > )   
   > SELECT DISTINCT * FROM CTE   
   > WHERE LastName IS NOT NULL   
   > ORDER BY Source, AnswerText   
   >   
      
   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.   
      
   > Some notes:   
   >   
   > 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.   
      
      
   > 2) I had to put the DISTINCT to avoid that Larry Marks appeared twice   
   >   in the result set; I did not look into very closely why he did.   
   >   
   > I think you agree that the UNION query is much cleaner.   
      
   I agree.   
      
   Just want to look at some other options.   
      
   Thanks,   
      
   Tom   
   >   
   >   
   > --   
   > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
   >   
   > Links for SQL Server Books Online:   
   > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)