Outer Join Problem -- Please advise

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

SQL Server Development (2000)

Outer Join Problem -- Please advise

Author

Topic

mharris
Starting Member

6 Posts

Posted - 2001-12-05 : 14:37:42

Hello

I have two tables, a questions table and an answers table. answers are linked to questions by questionID, and every answer row has a usrid field as well identifying the user. Questions are also linked to trivias by triviaID (a third table).

Given a usrID, I need to be able to get both the question info and answer info for every question for a given trivia. The complicated part happens if a user did not answer some questions--I need to also get question/answer combo for questions the user didnt answer(so much for normal innner join). Here is what I have so far:

SELECT q.question, a.correct
FROM triviaquestion q LEFT OUTER JOIN generic_triviaanswer a on q.questionID = a.questionID
WHERE (a.usrID = #cfvariable# OR a.usrID is NULL) AND q.triviaID = #someothervar#
ORDER BY a.date

The a.usrID is NULL part of the where clause does not return all the rows that the user did not answer; I think it gets any questions that NO users answered, which in anything but a test case will be a null set.

Any suggestions are welcome. Thanx in advance.

m

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-05 : 15:23:06

Put the test for the user ID in the ON condition.


SELECT q.question, a.correct
FROM triviaquestion q
LEFT OUTER JOIN generic_triviaanswer a
  ON q.questionID = a.questionID AND a.usrID = #cfvariable#
WHERE q.triviaID = #someothervar#
ORDER BY a.date

Factoid: The singular of trivia is trivium.

Edited by - Arnold Fribble on 12/05/2001 15:25:36

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-05 : 18:15:29

From Merrium Websters:

Main Entry: fac·toid
Pronunciation: 'fak-"toid
Function: noun
Date: 1973
1 : an invented fact believed to be true because of its appearance in print

Damian

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-06 : 03:33:57

Yes, I edited it to that when I realized I was probably talking out of my hat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources