Joining Table on Multiple Fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining Table on Multiple Fields

Author

Topic

BogeyGrinder
Starting Member

6 Posts

Posted - 2011-03-17 : 10:34:25

Using the examples below, I need a query to join Records to Survey answers, either on CustomerID or PurchaseID. Note, neither CustomerID or PurchaseID are required to be in the SurveyAnswers table, and one can exist there without the other. Can anyone assist?

In the Records table, both CustomerID and PurchaseID will always be present.

Expected Results (should match # of rows in Records table):

CustomerID, PurchaseID, Question1_Answer, Question2_Answer
100, 999, Yes, NULL
100, 888, Yes, NULL
200, 777, No, Disappointed
200, 666, No, NULL
300, 555, NULL, NULL
400, 444, NULL, Satisfied

-------
[CODE]
-- Note - QuestionID will always be 1 or 2
CREATE TABLE SurveyAnswers (
QuestionID INT,
CustomerID INT,
PurchaseID INT,
Answer NVARCHAR(50));

CREATE TABLE Records (
CustomerID INT,
PurchaseID INT);

INSERT INTO Records (CustomerID, PurchaseID) VALUES ('100', '999');
INSERT INTO Records (CustomerID, PurchaseID) VALUES ('100', '888');
INSERT INTO Records (CustomerID, PurchaseID) VALUES ('200', '777');
INSERT INTO Records (CustomerID, PurchaseID) VALUES ('200', '666');
INSERT INTO Records (CustomerID, PurchaseID) VALUES ('300', '555');
INSERT INTO Records (CustomerID, PurchaseID) VALUES ('400', '444');

INSERT INTO SurveyAnswers (QuestionID, CustomerID, Answer) VALUES ('1', '100', 'Yes');
INSERT INTO SurveyAnswers (QuestionID, CustomerID, Answer) VALUES ('1', '200', 'No');
INSERT INTO SurveyAnswers (QuestionID, PurchaseID, Answer) VALUES ('2', '555', 'Pleased');
INSERT INTO SurveyAnswers (QuestionID, PurchaseID, Answer) VALUES ('2', '777', 'Disappointed');
INSERT INTO SurveyAnswers (QuestionID, PurchaseID, Answer) VALUES ('2', '666', 'Very nice');
INSERT INTO SurveyAnswers (QuestionID, PurchaseID, Answer) VALUES ('2', '444', 'Satisfied');
[/CODE]

BogeyGrinder
Starting Member

6 Posts

Posted - 2011-03-17 : 10:39:47

I can't seem to edit the original post. I made a mistake. QuestionID in SurveyAnswers can be any number from 1-10... but I only want to return results where QuestionID = 1 or QuestionID = 2. That's the part that is throwing me off.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 11:21:37

SELECT r.customerid,r.purchaseid
,[Question1_Answer] = MAX(CASE WHEN s.questionId = 1 THEN Answer END)
,[Question2_Answer] = MAX(CASE WHEN s.questionId = 2 THEN Answer END)
FROM #Records R
INNER JOIN #SurveyAnswers s
ON
( r.customerid = s.customerid
or r.purchaseid = s.purchaseid)
WHERE s.questionid < 3
GROUP BY r.customerid,r.purchaseid
ORDER BY customerid

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources