Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

creating a view to include fields which are NULL Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
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).
Post #1337768
Posted Tuesday, July 31, 2012 6:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1337830
Posted Tuesday, July 31, 2012 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
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

Post #1337960
Posted Tuesday, July 31, 2012 1:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1338152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse