This statement works on two of the sub-queries, but not on the third and I am not sure why - the only difference between the third and other two queries is a different value check on a different field.
I have 3 tables (there are in fact 20, joins but it gets very complicated so I am going to reduce this to just three to make the issue clearer. Before anyone mentions OR's - it won't work with this query the way I need it to work.
PROPERTY
ASSESSMENTSUMMARY
ANSWER
ASSESSMENTSUMMARY table has a PROPERTYREF which is linked to the PROPERTY table.
ANSWER table has an assessmentsummaryref which is linked to the assessmentsummary table.
I am looking for the same propertyref in my main query and two different questionheadingref's.
Then I am performing 3 sub-queries and if all 3 match my request, I get one record back from my main select query (not shown, but it could be anything, i.e. select Property.Propertyref for example).
WHERE (Property.PropertyRef IN (40359)) AND (AssessmentSummary.QuestionHeadingRef IN (64900016, 64900017)) AND EXISTS
(SELECT *
FROM [answer]
WHERE AssessmentSummaryref = AssessmentSummary.AssessmentSummaryref AND [answer].[questionref] = 64901184 AND [Answer].[YES] = 1)
AND EXISTS
(SELECT *
FROM [answer]
WHERE AssessmentSummaryref = AssessmentSummary.AssessmentSummaryref AND [answer].[questionref] = 64901185 AND [Answer].[NO] = 1)
AND EXISTS
(SELECT *
FROM [answer]
WHERE AssessmentSummaryref = AssessmentSummary.AssessmentSummaryref AND [answer].[questionref] = 64901222 AND [Answer].[NO] = 1)
If I remove the last query, it runs and I get a record back - if I change one of the [YES] or [NO] values in those first two sub-queries, the record does not come back (correct) and if I put it back in to what it was, it runs.
If I remove the first two queries and leave the last one in, I get a record. If i change the value of the last query from a [NO] to a [YES] I get a result.
So I know all 3 sub queries work, validated not only through the sub queries themselves when run separately, but also when I check the physical data.
All 3 sub queries belong to propertyref 40359.
Questionref's
64901184
and
64901185
belong to the same questionheading ref, 64900016, while the third sub-query's question ref of
Hide Copy Code
64901222
belongs to 64900017.
Put them all together and I get no rows back, regardless of what the values are.
The only difference between the questionref value in first, second and third sub queries, is that the third sub query belongs to questionheadingref 64900017.
I cannot see what is wrong here but to be honest, I have been trying to figure this out for a few hours now and at this point - Im not seeing the wood for the trees so am probably missing something so obvious that I will slap my head for being such a naughty boy.
Any ideas SQL masters? Thanking you in advance