Joining from 2 different tables

Giganews Newsgroups
Subject: Joining from 2 different tables
Posted by:  tshad (t…@dslextreme.com)
Date: Thu, 21 Oct 2010

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.

Replies