SQL EXISTS conundrum

Transact-SQL
1

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

2

Do all three rows in [answer] exist for both values of (AssessmentSummary.QuestionHeadingRef IN (64900016, 64900017))? Naturally the EXISTS has to be true for every row in the main query, not just 2 exists for one value in AssessmentSummary and one exists for the other value.

Btw, you can combine the lookups in the EXISTS into a single query, for efficiency, although that changes it from an EXISTS to a simple "=" condition:

AND 3 = 
(SELECT 
    MAX(CASE WHEN ([answer].[questionref] = 64901184 AND [Answer].[YES] = 1) 
             THEN 1 ELSE 0 END) +
    MAX(CASE WHEN ([answer].[questionref] = 64901185 AND [Answer].[NO] = 1)  
             THEN 1 ELSE 0 END) +
    MAX(CASE WHEN ([answer].[questionref] = 64901185 AND [Answer].[NO] = 1)  
             THEN 1 ELSE 0 END)
FROM [answer]
WHERE AssessmentSummaryref = AssessmentSummary.AssessmentSummaryref 
AND ([answer].[questionref] = 64901184 AND [Answer].[YES] = 1) OR
    ([answer].[questionref] = 64901185 AND [Answer].[NO] = 1)  OR
    ([answer].[questionref] = 64901222 AND [Answer].[NO] = 1) 
)
1 Like
3

Hi Scott, thanks for getting back to me.

[Answer] exists in all 3 records, they could have [YES] [NO] [NA] (I personally would have gone for an integer field of 1-3 and marked it as (0 for non-answer, 1 for yes, 2 for no and 3 for na) - but im working on an old legacy database that I cannot change the structure - so it is what it is.

I had to modify your reply-code as you're pointing to 64901185 twice (in case 2 and 3, so changed the 3rd one to 6490122) - it works fine - you're a superstar !. Am just trying to re-read and understand what its doing, because I am a .net programmer and the code is a report-generator allowing users to dynamically add as many of these sub-queries through a UI, I need to figure out how to add your method of sql into my code (as there could be 200 sub-queries in effect) :S mind boggling.

Anyway - thank you very much for the reply - if the exists didnt have to cross over questionheadingref's - it works fine, but our clients are a right pain in the you know what and want it to be oh-so-flexible that they have no idea what is involved. Thank you again Scott, most appreciated

4

Just coming back to your reply if you don't mind Scott.

What was wrong with my EXISTS statement, my select statement and main where clause was:
WHERE [Property].[Propertyref] =40359 AND [Assessmentsummary].[QuestionHeadingRef] IN (64900016, 64900017)

So what I could see from it, was that I did include both questionheadingrefs' in there, just for some reason, the EXISTS statement only wanted to look at the one.