Sql子查询EXISTS难题 - 沮丧超越信仰！ [英] Sql subquery EXISTS conundrum - frustrated beyond belief !
问题描述
我重写了这个问题，因此更容易理解 - 我相信如果你是一个SQL向导 - 你会立即发现问题。
两张桌子：
_A
和
_B
_A有2个字段，assessmentummaryref和questionheadingref（都是int）
_B有4个字段，assessmentummaryref，questionref，YES和NO（忽略模式，这是遗留数据库，我不能改变这个） 。
在表_A中我有2条记录。
第1条记录：assessmentummaryref = 1，questionheadingref为64900016
第二条记录：assessummaryref = 1，questionheadingref为64900017
table _B有3条记录。
第1条记录：assessmentummaryref = 1，questionref has 64901184，YES = 1且NO为空。
第2条记录：assessmentummaryre = 1，questionref有64900185，YES为NULL且NO = 1
第3条记录：assessmentsummaryref = 2，questionref有64901222，是的s NULL和NO = 1
所以我正在尝试做什么，（请不要向我提供计数声明，我需要EXISTS命令 - 记住，我正在简化这个目的（目的是可能有200个支票，而不是3个......并且有20个表加入）。
那我是什么想要做的是，使用下面的语句查看我的记录:(运行没有错误）。查看多个questionheadingrefs（在我的主要语句中），然后执行EXISTS检查以查看questionheadingref下的questionref是否具有YES或NO的值为1的答案。如果满足所有3个存在语句，我想要一个记录返回。
有两种不同的assessmentummaryrefs以及两种questionheadingrefs。我有一种感觉这是问题吗？或许？
我必须再说一遍，因为你可能很明显地执行一些OR并计算得到3，这等于我之后的结果，但是不是我追求的结果 - 我已经简化了我正在处理的原始陈述并且使用OR的计数不会解决我的问题 - 但我认为有人知道为什么这不起作用并希望知道一个EXISTS解决方案将工作。我真的很难征服这个，所以如果你征服了它，你将成为我生命的霹雳;）
如果你有一个转到此处 - 取出第三个AND EXISTS向前声明，并弹出一条记录。它与questionheadingref有所不同 - 即使所有数据都匹配。我完全糊里糊涂。
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]。[assessmentummaryref] = [_A]。[assessummaryref]和[_B]。[questionref] = 64901184 AND [_B]。[YES] = 1）AND
EXISTS
（SELECT 1
FROM [_B]
WHERE [_B]。[assessummaryref] = [_A]。[assessummaryref]和[_B]。[questionref] = 64901185 AND [_B]。[NO] = 1）
AND
EXISTS
（SELECT 1
FROM [_B]
WHERE [_B]。[assessummaryref] = [_A]。[assessmentummaryref]和[_B]。[questionref] = 64901222 AND [_B]。[NO] = 1）
我注意到的一件事是，如果我在每个EXISTS语句中指定了assessmentummaryref，我确实得到了正确的记录被退回 - 但是，我不知道那些评估会是什么样的..
ie
WHERE [_B]。[assessummaryref] = 1 AND [_B]。[questionref] = 64901184 AND [_B]。[YES] = 1 AND EXISTS
WHERE [_B]。[assessummaryref] = 2 AND [_B]。[questionref] = 64901185 AND [_B]。[YES] = 1 AND EXISTS
WHERE [_B]。[assessummaryref] = 3 AND [_B]。[questionref] = 64901184 AND [_B]。[YES] = 1和EXISTS
这就是我使用
[_ B]的原因。[assessmentummaryref] = [ _A]。[assessmentummaryref]
因为我认为这需要ca.对我而言......
感谢您的时间 - 我真的很感激，我知道你们都是很忙:)
我尝试了什么：
移动值周围。和周围。和周围。和周围。很少有事情要考虑：
- 目前您不在子查询中使用别名。这可能会导致解析器的不确定性，您实际指的是哪一列。尝试使用格式
... AND EXISTS （ SELECT 1
FROM [answer]
WHERE [answer] .AssessmentSummaryref = AssessmentSummary.AssessmentSummaryref
AND [answer]。[questionref] = 64901184
AND [答案]。[是] = 1 ）... - 如果是和否是相互排斥的，使用单个字段。现在可能是你有错误的数据导致不合逻辑的结果
- 检查数据，你可以使用例如以下内容来确保数据是你期望的数据
SELECT [answer] .AssessmentSummaryref，[answer]。[questionref]，[Answer]。[YES]，[Answer]。 [否]
FROM [answer]
WHERE （[answer]。[ questionref] = 64901184 AND [答案]。[YES] = 1 ）
或（[answer]。[questionref] = 64901185 AND [答案]。[NO] = 1 ）
OR （[answer]。[questionref] = 64901222 AND [答案] 。[NO] = 1 ）
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.
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
... 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
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)
这篇关于Sql子查询EXISTS难题 - 沮丧超越信仰！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
