Styjun

Find records where value for supplied categories does not exist

So I have a requirement to produce a query that references data entered by users into a user form. The form is a tick box type and if no value is selected then a tick should be entered that says 'None of the above'. The form is divided into 4 different sections each needing a tick in relevant boxes or if no box is relevant then each section should have a tick in the 'None of the above' box for that section.

Now i am looking at the tables in the background and trying to construct a query that highlights records that do not have a tick in one of the boxes in one or more sections, not even the 'None of the above' box.

essentially we are looking to make sure that the form (each form has its own id) has been populated completely.

For the purposes of this site assume that my data is all in a single table:

Any idea how i can construct this query?

In the example above i would want to identify customer 789 as they are missing a value for section 3

this is a simplified version of what i am trying to do but hopefully will be clear

Ideally your input form would have validation that would not allow this. However, I realize that may not be under your control. This will work for the simplified example you provided. Hopefully you can modified it to your needs.

The syntax may vary depending on your database platform. This is on SQL Server.

CREATE TABLE #Section (ID INT)

INSERT INTO #Section (ID) VALUES (1)
INSERT INTO #Section (ID) VALUES (2)
INSERT INTO #Section (ID) VALUES (3)
INSERT INTO #Section (ID) VALUES (4)

CREATE TABLE #CustomerResponse (CustomerID INT, FormID INT, SectionID INT, value VARCHAR(25))

INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (789, 1, 1, '1')
INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (789, 1, 2, 'none of the above')
INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (789, 1, 4, '1')
INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (454, 2, 1, '1')
INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (454, 2, 2, '1')
INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (454, 2, 3, '1')
INSERT INTO #CustomerResponse (CustomerID, FormID, SectionID, value) VALUES (454, 2, 4, '1')

SELECT DISTINCT cr1.CustomerID, cr1.FormID, 1 AS MissingSection FROM #CustomerResponse cr1
LEFT JOIN #CustomerResponse cr2 ON cr1.CustomerID = cr2.CustomerId AND cr2.SectionID = 1
WHERE cr2.CustomerID IS null
UNION
SELECT DISTINCT cr1.CustomerID, cr1.FormID, 2 AS MissingSection FROM #CustomerResponse cr1
LEFT JOIN #CustomerResponse cr2 ON cr1.CustomerID = cr2.CustomerId AND cr2.SectionID = 2
WHERE cr2.CustomerID IS null
UNION
SELECT DISTINCT cr1.CustomerID, cr1.FormID, 3 AS MissingSection FROM #CustomerResponse cr1
LEFT JOIN #CustomerResponse cr2 ON cr1.CustomerID = cr2.CustomerId AND cr2.SectionID = 3
WHERE cr2.CustomerID IS null
UNION
SELECT DISTINCT cr1.CustomerID, cr1.FormID, 4 AS MissingSection FROM #CustomerResponse cr1
LEFT JOIN #CustomerResponse cr2 ON cr1.CustomerID = cr2.CustomerId AND cr2.SectionID = 4
WHERE cr2.CustomerID IS null

I do not like this solution, but it does work. Maybe someone will come along with something better.