My inner join isn't workin! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

My inner join isn't workin!

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-13 : 13:50:00

have two database tables "Questions" and "Answers". Questions contains a group of predefined questions that users can answer. "Answers" contains the user responses. The user can answer one of the questions, or all. It doesn't matter.

I want to get all the questions, and the answers that the user has happened to fill out.

If I run this Query I get the following result set:

select * from wm_questions Q
left join wm_user_answers A on Q.id = A.questionId

Here is the Results:

ID QUESTION ANSWER USERID

1 If I could live anywhere in the world NULL 66 1 asdfasdfasdf
1 If I could live anywhere in the world NULL 108 1 Fart Music!
2 My favorite food is NULL 66 2 Pizza
3 My favorite movie is NULL 66 3 Mad Max
4 My favorite bands are NULL 66 4 Wilco
5 My favorite person is NULL NULL NULL NULL
6 Coffee, Tea, or vodka NULL NULL NULL NULL
7 Things that gross me out NULL NULL NULL NULL
8 I am a sucker for NULL NULL NULL NULL
9 My biggest fear is NULL NULL NULL NULL
10 My biggest Pet Peave is NULL NULL NULL NULL
11 Big partier or stay at home type NULL NULL NULL NULL
12 The stupidest thing I have ever done NULL NULL NULL NULL
13 My most humiliating moment NULL NULL NULL NULL
14 My biggest accomplishment NULL NULL NULL NULL

I would think adding a where clause would do what I want, but it just limist the results to ONLY THE QUESTIONS THAT THE USER HAS ANSWERSD.

I need all the questions, the related answers if given. What am i doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 13:52:50

LEFT OUTER JOIN
Read all about it in Books Online.

Peter Larsson
Helsingborg, Sweden

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-13 : 14:07:51

Thanks Peter,

I changed the query to look like this:

select * from wm_questions Q
left outer join wm_user_answers A on Q.id = A.questionId
where A.userid=66

and I get only 4 results. They are the questions with answers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 14:15:21

You need to learn you NULLs.

select * from wm_questions AS Q
left outer join wm_user_answers AS A on A.questionId = Q.id AND A.userid=66

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-13 : 14:16:26

You sure?

That should give you every question in the question table and the answers for user 66 that answered, and the rest should be null

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

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-13 : 16:47:54

Cool - that did work! That makes sense to me now; the AND clause only applies to the answers table. thanks for the explanation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources