From: tfs@dslextreme.com   
      
   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.   
      
   The following are not the actual tables but just an example of what I am   
   trying to do.   
      
   CREATE TABLE Questions   
   (   
       QuestionID int,   
       QuestionText varchar(2000)   
   )   
      
   CREATE TABLE Answers   
   (   
       AnswersID int,   
       QuestionID int,   
       AnswerText varchar(2000)   
   )   
      
   CREATE TABLE Employees   
   (   
       EmployeeID int   
       FirstName varchar(30),   
       LastName   
   )   
      
   CREATE TABLE EmployeeAnswerMap   
   (   
       EmployeeAnswerMapID int,   
       EmployeeID int,   
       AnswerID int   
   )   
      
   CREATE TABLE Customers   
   (   
       CustomerID int   
       FirstName varchar(30),   
       LastName   
   )   
      
   CREATE TABLE CustomerAnswerMap   
   (   
       CustomerAnswerMapID int,   
       CustomerID int,   
       AnswerID int   
   )   
      
   I could do it this way, where the 1st query gets me all the answers that the   
   Employee answered and the 2nd one gets me all the answers that a Customer   
   answered:   
      
   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   
   UNION ALL   
   SELECT FirstName, LastName, QuestionText, AnswerText   
   FROM Questions Q   
   JOIN Answers A ON A.QuestionID = Q.QuestionID   
   JOIN CustomerAnswerMap CAM.AnswerID = A.AnswerID   
   JOIN Customers C ON C.CustomerID = CAM.CustomerID   
      
   But I was trying to do it withoug a UNION.  Maybe you can't.   
      
   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.   
      
   Thanks,   
      
   Tom.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)