I have re-wrote this question so it is much easier to understand - I am sure that if you're a SQL wizard - you'll spot the issue straight away.
Two Tables:
_A
and
_B
_A has 2 fields, assessmentsummaryref and questionheadingref (both int)
_B has 4 fields, assessmentsummaryref, questionref, YES and NO (ignore the schema, this is a legacy database and I cannot go changing this).
In table _A I have 2 records.
1st record: assessmentsummaryref=1, questionheadingref of 64900016
2nd record: assessmentsummaryref=1, questionheadingref of 64900017
table _B has 3 records.
1st record: assessmentsummaryref=1, questionref has 64901184, YES=1 and NO is a null.
2nd record: assessmentsummaryre=1, questionref has 64900185, YES is NULL and NO=1
3rd record: assessmentsummaryref=2, questionref has 64901222, YES is NULL and NO=1
so what I am trying to do, (please do not present me with a statement for counting, I need to the EXISTS command - remember, I am simplifying this for a purpose (purpose being there may be 200 checks, not 3... and there are 20 tables joined)).
So what I want to do, is look at my records using the statement below: (which runs without error). Is look at multiple questionheadingrefs (in my main statement), then do an EXISTS check to see if the questionref under the questionheadingref has an answer of YES or NO with a value of 1. If all 3 exists statements are met, I want a record returning.
There are two different assessmentsummaryrefs as well as two questionheadingrefs. I have a feeling this is the problem ? perhaps?
I must say this again because it may be obvious to you to perform some OR's and counting to get 3 which would equal the result I am after, but that is not the result I am after - I have simplified the original statement I am working on and a count with OR's is not going to solve my issue - but I think someone knows the reason why this does not work and hopefully knows an EXISTS solution that will work. I've really had a hard time trying to conquer this so if you do conquer it, you'll be my thunderbuddy for life ;)
If you do have a go at this - take the third AND EXISTS onwards statement out and a record will pop back. Its something to do with the questionheadingref being different - even though all the data matches. Im totally confuddled with it.
SELECT DISTINCT _A.assessmentsummaryref
FROM _A INNER JOIN
_B ON _A.assessmentsummaryref = _B.assessmentsummaryref
WHERE (_A.questionheadingref IN (64900016, 64900017)) AND EXISTS
(SELECT 1
FROM [_B]
WHERE [_B].[assessmentsummaryref] = [_A].[assessmentsummaryref] AND [_B].[questionref] = 64901184 AND [_B].[YES] = 1) AND
EXISTS
(SELECT 1
FROM [_B]
WHERE [_B].[assessmentsummaryref] = [_A].[assessmentsummaryref] AND [_B].[questionref] = 64901185 AND [_B].[NO] = 1)
AND
EXISTS
(SELECT 1
FROM [_B]
WHERE [_B].[assessmentsummaryref] = [_A].[assessmentsummaryref] AND [_B].[questionref] = 64901222 AND [_B].[NO] = 1)
One thing I have noticed - is that IF I specify the assessmentsummaryref in each of the EXISTS statements, I do get the correct record being returned - however, I wont know what those assessmentsummaryrefs are going to be..
i.e.
WHERE [_B].[assessmentsummaryref] = 1 AND [_B].[questionref] = 64901184 AND [_B].[YES] = 1 AND EXISTS
WHERE [_B].[assessmentsummaryref] = 2 AND [_B].[questionref] = 64901185 AND [_B].[YES] = 1 AND EXISTS
WHERE [_B].[assessmentsummaryref] = 3 AND [_B].[questionref] = 64901184 AND [_B].[YES] = 1 AND EXISTS
Thats why I used
[_B].[assessmentsummaryref] = [_A].[assessmentsummaryref]
as I thought this would take care of that for me...
Thank you for your time - I really do appreciate it, I know you're all very busy :)
What I have tried:
moving values around. and around. and around. and around. and around.