From: tfs@dslextreme.com   
      
   "Roger"  wrote in message   
   news:spOdnblnYfY0BVzRnZ2dnUVZ7qWdnZ2d@bt.com...   
   > "tshad"  wrote in message   
   > news:1k5wo.17791$qz.10422@newsfe04.iad...   
   >>I can't seem to get this query to work. I have a set of tables where I   
   >>have questions in one table.  Each question can have multiple possible   
   >>answers. Either employees or customers can answer the questions.   
   >>   
   >> What I need is a row for each question answered and the person that   
   >> answered it.  If both an employee and a customer answered the question   
   >> then I should have 2 rows.   
   >>   
   >> But if there is an answer that no one answered, that answer shouldn't   
   >> show.   
   >   
   >   
   >> If I do the following I only get the ones that match both the employee   
   >> and customer.   
   >>   
   >> SELECT FirstName, LastName, QuestionText, AnswerText   
   >> FROM Questions Q   
   >> JOIN Answers A ON A.QuestionID = Q.QuestionID   
   >> JOIN EmployeeAnswerMap EAM.AnswerID = A.AnswerID   
   >> JOIN Employees E ON E.EmployeeID = EAM.EmplyeeID   
   >> JOIN CustomerAnswerMap CAM.AnswerID = A.AnswerID   
   >> JOIN Customers C ON C.CustomerID = CAM.CustomerID   
   >>   
   >> You can't do left join on either the EmployeeAnswerMap or the   
   >> CustomerAnswerMap or you would get all the answers whether anybody   
   >> answered them or not.   
   >>   
   >> What I am looking for are the answers that either an employee or a   
   >> customer (or both) have answered.   
   >   
   > Hi  Tom,   
   >   
   > 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.   
      
   Thanks,   
      
   Tom   
   > but then you will need two sets of columns for employee and customer   
   > i.e. select c.firstname, c.lastname, e.firstname, e.lastname   
   >   
   > and you'll get one row for customer, or employee, or both   
   >   
   > If you want only one firstname and one lastname column, and two rows when   
   > both answer the question   
   > then I think a union is the answer   
   >   
   > Roger   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)