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.

tshad (tfs (AT) dslextreme (DOT) com) writes:
I think you are, but if I get this right you get them them columnwise,
rather than rowwise. To get them rowwise, you need to unpivot. This
may be possible by cross-joining with this table

(SELECT 1 AS n UNION ALL SELECT 2) AS u

And then do some CASE acrobatics. Since you did not provide any sample
data, I abstain from trying a full query.

After all, the UNION query seems a lot simpler.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

"tshad" <tfs (AT) dslextreme (DOT) com> wrote

Do you mean questions without any answers, or answers that have not been
chosen?

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.

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

--
Roger

"Roger" <mothland (AT) btopenworld (DOT) com> wrote

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.

I agree - but the second table was added by someone else.


Thanks,

Tom

"tshad" <tfs (AT) dslextreme (DOT) com> wrote



Yes

That's probably easier without a union

select whatever from answers a
left join employeeanswermap eam on a.answersid = eam.answerid
left join customeranswermap cam on a.answersid = cam.answerid
where cam.customeranswermapid is null and eam.employeeanswermapid is null


Isn't that always the way

Roger

tshad (tfs (AT) dslextreme (DOT) com) writes:
Actually, it is worth to take a look it. For this particular problem, the
query is overly complicated, but the technique as such comes to use at
times. That is, when you need to convert column to rows, the typical
technique is to cross join with a small table of constants and then use
these constants to control CASE expressions. (There is also the UNPIVOT
keyword, but I've learnt that syntax very well.)

When you have:

LEFT JOIN EmployeeAnswerMap EAM ON EAM.AnswerID = A.AnswersID
JOIN Employees E ON E.EmployeeID = EAM.EmployeeID

You are effectively transforming the outer join to an inner join, because
the join to Employees filters out rows where EAM.AnswerID is NULL. Your
remedy was to use a second LEFT JOIN. That works too in this case, but
there are situations where this give a different result. (Which may be
considered far-fetch corner cases.)



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx