From: tfs@dslextreme.com   
      
   "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.   
      
   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.   
      
   > If the former, here's one way of doing it.   
   > Select the union of all questions that have been answered by customer or   
   > empoyee   
   > Then exclude those questions from the result   
   >   
   > SELECT     q.QuestionID, q.QuestionText   
   > FROM         Questions q LEFT JOIN   
   >    (    SELECT     a.QuestionID   
   >         FROM          Answers a INNER JOIN   
   >                                                   EmployeeAnswerMap AS eam   
   > ON a.AnswersID = eam.AnswerID   
   >    UNION   
   >        SELECT     a.QuestionID   
   >        FROM         Answers a INNER JOIN   
   >                                                  CustomerAnswerMap AS cam   
   > ON a.AnswersID = cam.AnswerID)   
   >    AS ans ON q.QuestionID = ans.QuestionID   
   > WHERE     (ans.QuestionID IS NULL)   
   >   
   > You could simplify things a lot if you had just one answermap table.   
   >   
      
   I agree - but the second table was added by someone else.   
      
   > Then either   
   > 1. have two columns for customerid and employeeid, and join on one or the   
   > other as required to get the customer/employee names   
   > or   
   > 2. have one table for both customers and employees, and add a field to   
   > tell which is which   
   >   
   Or have a 2nd UNION where this query is added to the original union.   
      
   Thanks,   
      
   Tom   
      
   > --   
   > Roger   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)