creating a view to include fields which are NULL

Question

Post reply

creating a view to include fields which are NULL

mrichardson 57577

Right there with Babe

Points: 739
More actions
July 31, 2012 at 4:24 am

#279025

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).

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Put in a specific check for NULL:

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

AND (WebFormData_2.WebFormFieldId = 98 OR WebFormData_2.WebFormFieldId IS NULL)

INNER JOIN db.WebFormData WebFormData_7

ON db.R.WebFormRowId = WebFormData_7.WebFormRowId

AND (WebFormData_7.WebFormFieldId = 99 OR WebFormData_7.WebFormFieldId IS NULL)

- with INNER JOINs, this can be in the ON or the WHERE clause.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mrichardson 57577 Right there with Babe Points: 739 More actions · Answer 2

this is working now.

used:

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

mrichardson 57577 (7/31/2012)
this is working now.
used:
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

Just a quick adjunct - "TOP 100 PERCENT" is completely redundant in this query.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin