From: tfs@dslextreme.com   
      
   "Roger"  wrote in message   
   news:U9WdnX7HyOFFI1jRnZ2dnUVZ7tudnZ2d@bt.com...   
   > "tshad"  wrote in message   
   > news:VQhxo.6609$jw4.2678@newsfe21.iad...   
   >>   
   >> "Roger"  wrote in message   
   >> news:v_SdnZAQYr2PAljRnZ2dnUVZ8sCdnZ2d@bt.com...   
   >>> "tshad"  wrote in message   
   >>> news:q5gxo.6227$yw.2591@newsfe18.iad...   
   >>>> That was what I figured but was curious if there was a better way,   
   >>>> performance wise.   
   >>>>   
   >>>> I have 2 queries I need to do.  One like the one I just did and one   
   >>>> that also shows the rows that didn't have any answers.   
   >>>>   
   >>>> If I was doing it without the UNION, I would just look at rows with   
   >>>> nulls in both sets of columns.   
   >>>>   
   >>>> But what is the best way using a UNION?   
   >>>>   
   >>>   
   >>> Do you mean questions without any answers, or answers that have not been   
   >>> chosen?   
   >>>   
   >> By the former, I assume you mean questions where none of the answers have   
   >> been chosen.   
   >   
   >   
   >   
   > Yes   
   >   
   >> So in my example, I would get an empty set.  But if I add the following   
   >> records:   
   >>   
   >> INSERT Questions(QuestionText) VALUES ('Which is your favorite sport?');   
   >> INSERT Answers (QuestionID,AnswerText) VALUES (1,'Football');   
   >> INSERT Answers (QuestionID,AnswerText) VALUES (1,'Baseball');   
   >> INSERT Answers (QuestionID,AnswerText) VALUES (1,'Basketball');   
   >> INSERT Answers (QuestionID,AnswerText) VALUES (1,'Other');   
   >>   
   >> Then I get "Which is your favorite sport?", which would be correct.   
   >>   
   >> Answers that no one has chosen (in my case that would have been Blue for   
   >> the original question and the all 4 answers for the new question since no   
   >> one had answered it) would also be useful.   
   >   
   > That's probably easier without a union   
   >   
   > select whatever from answers a   
   > left join employeeanswermap eam on a.answersid = eam.answerid   
   > left join customeranswermap cam on a.answersid = cam.answerid   
   > where cam.customeranswermapid is null and eam.employeeanswermapid is null   
   >   
      
   Right, but if I want it in the same report, I would 2 unions where this   
   select would be the last select as in:   
      
      
   SELECT FirstName, LastName, QuestionText, AnswerText, 'Employees' as Source   
   FROM Questions Q   
   JOIN Answers A ON A.QuestionID = Q.QuestionID   
   JOIN EmployeeAnswerMap EAM ON EAM.AnswerID = A.AnswersID   
   JOIN Employees E ON E.EmployeeID = EAM.EmployeeID   
   UNION ALL   
   SELECT FirstName, LastName, QuestionText, AnswerText, 'Customer'   
   FROM Questions Q   
   JOIN Answers A ON A.QuestionID = Q.QuestionID   
   JOIN CustomerAnswerMap CAM ON CAM.AnswerID = A.AnswersID   
   JOIN Customers C ON C.CustomerID = CAM.CustomerID   
   UNION ALL   
   select '', '', questionText, AnswerText,''   
   from answers a   
   JOIN Questions q on q.QuestionID = a.QuestionID   
   left join employeeanswermap eam on a.answersid = eam.answerid   
   left join customeranswermap cam on a.answersid = cam.answerid   
   where cam.customeranswermapid is null and eam.employeeanswermapid is null   
      
   But I did see that in my query as in yours I got the same Question for all   
   the answers in both my query and yours and realized I made an error in the   
   extra inserts for the Answers.  They should be 2 intead of 1.   
      
   INSERT Answers (QuestionID,AnswerText) VALUES (2,'Football');   
   INSERT Answers (QuestionID,AnswerText) VALUES (2,'Baseball');   
   INSERT Answers (QuestionID,AnswerText) VALUES (2,'Basketball');   
   INSERT Answers (QuestionID,AnswerText) VALUES (2,'Other');   
      
   Thanks,   
      
   Tom   
   >   
   >>>   
   >>> You could simplify things a lot if you had just one answermap table.   
   >>>   
   >>   
   >> I agree - but the second table was added by someone else.   
   >>   
   >   
   > Isn't that always the way   
   >   
   > Roger   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)