From: tfs@dslextreme.com   
      
   "Roger"  wrote in message   
   news:_KGdnWqmZNJb-FjRnZ2dnUVZ8sidnZ2d@bt.com...   
   >   
   > "tshad"  wrote in message   
   > news:736xo.6221$yw.5388@newsfe18.iad...   
   >>   
   >> "Roger"  wrote in message   
   >> news:4_udnZzfXe6Ekl7RnZ2dnUVZ7qGdnZ2d@bt.com...   
   >>> "tshad"  wrote in message   
   >>> news:Zwowo.4911$jw4.3982@newsfe21.iad...   
   >>>>   
   >>>>>   
   >>>>> You could use a left join and exclude the empty rows in the where   
   >>>>> clause...   
   >>>>>   
   >>>>> left JOIN EmployeeAnswerMap on EAM.AnswerID = A.AnswerID   
   >>>>> left JOIN CustomerAnswerMap on CAM.AnswerID = A.AnswerID   
   >>>>> where not (EAM.AnswerID is null and CAM.AnswerID is null)   
   >>>>>   
   >>>>   
   >>>> That was what I tried.   
   >>>>   
   >>>> But I don't seem to get all of them.   
   >>>>   
   >>>> If I do a UNION, I get 11 records.  If I do each separately, I get 6   
   >>>> for employees and 5 for customers.   
   >>>>   
   >>>> But if I do the left Joins, I am only getting 7 records.   
   >>>>   
   >>>> Can't figure out why that is not getting the other 4.   
   >>>   
   >>> Hi Tom,   
   >>>   
   >>> That's probably correct.   
   >>>   
   >>> If some questions are answered by both customer and employee, you will   
   >>> only get one row for each answer   
   >>>   
   >>   
   >> You're right.  Which is why I sent a schema with various queries that   
   >> show those issues.   
   >>   
   >> Maybe UNION is the best way to handle it but I just wanted to see if   
   >> there was a better way to handle it not using UNIONs.  As I mentioned in   
   >> the other post, the one without the UNION performs better but I don't get   
   >> the correct response.   
   >   
   > Hi Tom,   
   >   
   > If you want 1 row for each answer then union is the best way.   
   >   
   > The performance issue is because the union is doing twice as much work.   
      
   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?   
      
   I had used a temp table before to solve the problem but I hate using temp   
   tables.   
      
   Thanks,   
      
   Tom   
      
   >   
   > However, for info.   
   > In your left join query, if you want only questions anwered by either   
   > customer, or employee, or both   
   > You need to exclude the questions that neither answered...   
   >   
   > where not (EAM.AnswerID is null and CAM.AnswerID is null)   
   >   
   > Roger   
   >   
   >   
   >   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)