为什么查询输出中每个实体有多个条目?
想知道为什么我的查询在输出中显示每个实体的多个条目。 据我了解,每个实体只有一项有效政策。
使用 SQL Server Management Studio 创建查询,正确显示的输出具有参数,并且我已在查询中尝试了以下操作。
目前我的 SQL SSMS 查询输出显示以下内容:
Entity_Number Building_Name PolicyID Description Start_Date End_Date
400 Xpress 4 5 Day Grace 7/1/2019 9/27/2019
400 Xpress 18 2 Day Grace 7/3/2018 7/13/2018
400 Xpress 19 4 Day Grace 2/27/2019 2/27/2019
我真正想知道的是如何深入并找出查询返回倍数的原因?
[询问]
SELECT
e.Entity_Number,
bld.Building_Name,
cbp.PolicyId,
cbp.Description,
cbp.StartDate,
cbp.EndDate
FROM
dbo.buildings AS bld
INNER JOIN dbo.entities AS e
ON bld.Entity_ID = e.Entity_ID
INNER JOIN Collections.Building AS cbp
ON bld.Building_ID = cb.BuildingId
INNER JOIN Collections.BuildingProfile AS cbpro
ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId
WHERE
bld.Building_Active = 1
AND e.Active = 1
Would like to know why my query displays multiple entries per entity in the output.
From what I understand there is only one active policy per entity.
Created query with SQL Server Management Studio, my output to display correctly has parameters, and I have tried the following with my query.
Currently my SQL SSMS query output displays the following:
Entity_Number Building_Name PolicyID Description Start_Date End_Date
400 Xpress 4 5 Day Grace 7/1/2019 9/27/2019
400 Xpress 18 2 Day Grace 7/3/2018 7/13/2018
400 Xpress 19 4 Day Grace 2/27/2019 2/27/2019
What I really would like to know is how do I drill down and find out why my query returns multiples?
[Query]
SELECT
e.Entity_Number,
bld.Building_Name,
cbp.PolicyId,
cbp.Description,
cbp.StartDate,
cbp.EndDate
FROM
dbo.buildings AS bld
INNER JOIN dbo.entities AS e
ON bld.Entity_ID = e.Entity_ID
INNER JOIN Collections.Building AS cbp
ON bld.Building_ID = cb.BuildingId
INNER JOIN Collections.BuildingProfile AS cbpro
ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId
WHERE
bld.Building_Active = 1
AND e.Active = 1
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
使用“萨拉米技术”来隔离意外行的来源。我的意思是,通过一一省略每个连接(以及与该连接相关的任何列引用),可以像意大利腊肠一样减少查询。
例如,从屏蔽与
Collections.BuildingProfile
的连接开始:这是否会删除意外的列?如果没有,那么尝试:
最终通过屏蔽每个连接(以及对该表的任何相关列引用),您将发现哪个表正在产生意外的行乘法。
一旦确定了该表,我建议您重新考虑您对该表的连接方式所做的所有假设。例如,您声明“据我所知,每个实体只有一项有效策略。” 这真的是这样吗?
一旦您知道问题从哪里开始,您就可以重新考虑如何在查询中实际使用该数据,您应该更接近解决方案。例如,也许您需要在连接中添加更多条件,或者您需要连接子查询而不是直接连接到表。
注意:
反正?
Use the "salami technique" to isolate where the unexpected rows come from. What I mean by this is that you cut down the query like a salami by omitting each join (and any column references related to that join) one by one.
e.g. start with masking the join to
Collections.BuildingProfile
:Does this remove the unexpected columns? If not then try:
Eventually by masking out each join (and any related column references to that table) you will discover which table is producing the unexpected multiplication of rows.
Once that table is identified I suggest you reconsider all assumptions you have made about how that table had been joined. For example, you state that " From what I understand there is only one active policy per entity." Is that really true?
Once you know where the problem starts, and you reconsider how that data should actually be used within the query, you should be closer to a solution. e.g. perhaps you need more conditions in the join, or you need to join a subquery instead of directly to the table.
Note:
anyway?