Trickey (for beginner) outer join on simple tables

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 2005 Forums

Transact-SQL (2005)

Trickey (for beginner) outer join on simple tables

Author

Topic

inibhreaslain
Starting Member

19 Posts

Posted - 2007-11-07 : 16:15:21

I have two tables.

Questions: QuestionID, QuestionText, BadIfTrue
PolicyAnswers: PolicyNumber, QuestionID, Selected

I want to show all Rows from the Question table
and
Selected from PolicyAnswers IF a record exists for a given policy number.

PolicyNumber is taken in from user.
If the policyNumber is not in the PolicyAnswers table I want to show just the questions
If the policyNumber is in the system I want to show questions with whether they are selected or not.

I just want one set of question records returned (all of them) with associated ansewrs for a given policy.

I hope I'm making sense

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-07 : 16:17:34

SELECT * FROM Questions q LEFT JOIN PolicyAnswers a ON q.QuestionID = a.QuestionID

????

Read the hint link in my sig if you need to supply more detail

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

inibhreaslain
Starting Member

19 Posts

Posted - 2007-11-07 : 16:42:15

Question
How do I get all rows in Question table (regardless of policyNumber's existence), with Selected column from PolicyAnswers table given a specific policyNumber (if in table).

Tables

CREATE TABLE Questions(QuestionID tinyint, QuestionText varchar(255), BadIfTrue bit)
CREATE TABLE PolicyAnswers(PolicyNumber varchar(11), QuestionID tinyint, Selected bit)

Sample Data

INSERT INTO PolicyAnswers (PolicyNumber, QuestionID, Selected)
VALUES ('XOP12346578', 1, 1), ('XOP12346578', 2, 1), ('XOP12346578', 3, 0), ('QWS87654321', 1, 0)

INSERT INTO Questions(QuestionID tinyint, QuestionText varchar(255), BadIfTrue bit)
VALUES (1, 'Is building more than ten years old', 1),
(2, 'Is building made from brick', 0),
(3, 'Is alcohol served on premises', 1)
(4, 'Are security cameras present at each entrance/exit', 0)

My attempt

SELECT Questions.QuestionText, Questions.BadIfTrue, PolicyAnswers.Selected
FROM Questions LEFT OUTER JOIN PolicyAnswers ON Questions.QuestionID = PolicyAnswers.QuestionID
WHERE (PolicyAnswers.PolicyNumber = 'GFB12378654')

Expected Results
For a Policy Number not in PolicyAnswers table: (all questions - no answers)

'Is building more than ten years old', null
'Is building made from brick', 0, null
'Is alcohol served on premises', 1, null
'Are security cameras present at each entrance/exit', 0, null

For a Policy Number in PolicyAnswers table: (XOP12346578 from above)

'Is building more than ten years old', 1
'Is building made from brick', 0, 1
'Is alcohol served on premises', 1, 0
'Are security cameras present at each entrance/exit', 0, null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources