Click here to Skip to main content
15,881,455 members
Search within:


Sql subquery EXISTS conundrum - frustrated beyond belief !
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
SQL-Server
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.
Posted
Updated 24-Jan-17 2:44am
v4
Add a Solution
Comments
Andy Lanng 23-Jan-17 11:34am    
The syntax looks fine (although I question your table structure, but that's outside the scope of your question).
Have you tried changing the where clauses into joins?
It must be that data that's wrong. It looks like one of your answers might be null? That would cause any comparison with the '=' operand to return true.

You say you simplified this query for our benefit. Can you simplify your data for the same reason? If so, test the query again yourself. If you still get wrong answers then please post the whole query and the test data.
Member 12561559 24-Jan-17 4:45am    
data is fine, i've hand-modified the data so i could get results and then change values to not get results, as a test. I can't simplify the data otherwise that would defeat the objective, I'll see if I can explain it better, below - thanks for replying though - I do appreciate the time you put in to reply
NightWizzard 23-Jan-17 13:33pm    
Although all subqueries will return at least 1 record if executed separately there might no be a record that fullfills the main where clause in conjunction with all 3 subqueries. Did you check your data for this?
BTW: if you replace SELECT * with SELECT 1 in your subqueries your query will need less memory to execute and it will produce the same result.
Member 12561559 24-Jan-17 4:46am    
Data is fine, I'll change the SELECT * to a SELECT 1 to improve performance - can always do with performance improvements :) I'll see if I can put it better below or use the improve question option. Thanks for getting back
Jörgen Andersson 24-Jan-17 6:30am    
What values does Table _B have for [assessmentsummaryref] in your example?

1 solution

Few things to consider:

  • At the moment you don't use aliases in the subqueries. This may cause ambiquity to the parser, which column you're actually referring to. Try using format
    SQL
    ... AND EXISTS (SELECT 1
                FROM  [answer] 
                WHERE [answer].AssessmentSummaryref = AssessmentSummary.AssessmentSummaryref 
                AND   [answer].[questionref] = 64901184 
                AND   [Answer].[YES] = 1)...
  • If YES and NO are mutually exclusive, use a single field. Now the possibility is that you have false data causing the illogical result
  • Check the data, you can use for example the following to ensure that the data is what you expect it to be
    SQL
    SELECT [answer].AssessmentSummaryref, [answer].[questionref], [Answer].[YES], [Answer].[NO]
FROM  [answer]
WHERE ( [answer].[questionref] = 64901184 AND [Answer].[YES] = 1)
OR    ( [answer].[questionref] = 64901185 AND [Answer].[NO] = 1) 
OR    ( [answer].[questionref] = 64901222 AND [Answer].[NO] = 1)
 
Share this answer
 
Posted
Comments
Member 12561559 24-Jan-17 4:49am    
Data is fine, double check with a physical look plus I run your select statement, had to put in a join in the FROM section and include a Propertyref in the OR sections, but I had my 3 records back, with the YES, NO's correct. Going to explain it better by modifying my question, I know it will be me so thank you for getting back, your time is most appreciated.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise
Privacy
Cookies
Terms of Use
Last Updated 24 Jan 2017
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900