creating a view to include fields which are NULL

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)

creating a view to include fields which are NULL

Author

Topic

barnsley
Starting Member

34 Posts

Posted - 2012-07-31 : 06:45:20

Our webiste has several contact forms which all go into the same table.
We therefore have to create views to display the results from each webform (separating by fieldID and so on).

however, when a user leaves a field blank our fieldID does not record an entry and so the view fails to display the rest of the form.
part of the SQL code is below:

SELECT     TOP 100 PERCENT 
WebFormData_2.FieldValue AS Surname, 
WebFormData_7.FieldValue AS SpecialReq
FROM         db.R INNER JOIN
                      db.WebFormData WebFormData_2 ON db.R.WebFormRowId = WebFormData_2.WebFormRowId INNER JOIN
                      db.WebFormData WebFormData_7 ON db.R.WebFormRowId = WebFormData_7.WebFormRowId
WHERE     
(WebFormData_2.WebFormFieldId =98) AND 
(WebFormData_7.WebFormFieldId =99)

if we replace =99 with <>99
then this results in the form being displayed, but it also displays several times (with duplicate entries in the view).

mark.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 07:01:18

neither =99 nor <>99 will give true null values.
I suspect yoou might want

SELECT TOP 100 PERCENT
WebFormData_2.FieldValue AS Surname,
WebFormData_7.FieldValue AS SpecialReq
FROM db.R
JOIN db.WebFormData WebFormData_2
ON db.R.WebFormRowId = WebFormData_2.WebFormRowId
and WebFormData_2.WebFormFieldId =98
left JOIN db.WebFormData WebFormData_7
ON db.R.WebFormRowId = WebFormData_7.WebFormRowId
and WebFormData_7.WebFormFieldId =99

that will return a row if there is no WebFormData_7.WebFormFieldId =99

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

barnsley
Starting Member

34 Posts

Posted - 2012-07-31 : 10:54:06

thanks nigel,
that works a treat!

mark.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources