OUTER JOIN nulls in WHERE clause

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

Transact-SQL (2000)

OUTER JOIN nulls in WHERE clause

Author

Topic

julianfraser
Starting Member

19 Posts

Posted - 2004-07-09 : 09:23:47

I have a serious issue that I just can't seem to work out, maybe I've just been staring at this code too long, I dunno?

I have this select statement that selects responses to a survey for export.

#data_respondants is a list of all the the respondants to the survey
project_data contains the response data for the question specified in the WHERE clause

The problem is that project_data does not always contain a record that matches the project_respondantID and the tool_questionID, this occurs when the respondant has elected not to give a response to this question. However, the record for this respondant still needs to display but just showing a null value for the response. Because of the where clause, if there is no matching row in project_data then no record is displayed.

Any ideas?

SELECT dr.project_respondantID, pd.tool_questionID, tmo.option_title AS 'response'
FROM #data_respondants dr
LEFT JOIN project_data pd ON dr.project_respondantID = pd.project_respondantID
LEFT JOIN project_data_mch pdm ON pdm.project_dataID = pd.project_dataID
LEFT JOIN tool_mch_option tmo ON tmo.tool_mchID = pdm.tool_mchID
WHERE pd.tool_questionID = @tool_questionID

Thanks in advance,
Julian Fraser.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-09 : 09:27:03

Move the condition to the join.

SELECT dr.project_respondantID, pd.tool_questionID, tmo.option_title AS 'response'
FROM #data_respondants dr
LEFT JOIN project_data pd ON dr.project_respondantID = pd.project_respondantID
AND pd.tool_questionID = @tool_questionID
LEFT JOIN project_data_mch pdm ON pdm.project_dataID = pd.project_dataID
LEFT JOIN tool_mch_option tmo ON tmo.tool_mchID = pdm.tool_mchID

julianfraser
Starting Member

19 Posts

Posted - 2004-07-09 : 09:58:31

Nice one, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources