creating a view to include fields which are NULL

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

  • 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

  • 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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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