TSQL编码问题 [英] TSQL Coding question

问题描述

以下代码的目的是在SR_CONFIG表中存在匹配记录时在''HasConfig'列中显示'YES'，否则为值显示''NO''。这似乎并没有起作用，因为当我得到''是''和''不''的价值时，我得到所有''是''。有更好的方法吗？

The purpose of the following code is to display ''YES'' in the ''HasConfig'' column when there is a matching record the SR_CONFIG table, else display ''NO'' for a value. This does not seem to be working because I am getting all ''YES'' back when I should be getting both ''YES'' and ''NO'' values back. Is there a better way to do this?

SELECT (CASE
        WHEN EXISTS(SELECT V.SR_Service_RecID
                    FROM v_cbi_Tracked_Tickets V
                    LEFT JOIN SR_Config C
                    ON C.SR_Service_RecID = V.SR_Service_RecID)
        THEN 'YES'
        ELSE 'NO'
        END) AS 'HasConfig',V.*

        FROM v_cbi_Tracked_Tickets v

ALSO 我知道不同的联接做了什么，并且已经尝试了所有联接但是得到了相同的结果!!!

ALSO I know what the different joins do and have tried them all but keep getting the same results!!!

推荐答案

更改你的LEFT JOIN INNER JOIN。 LEFT JOIN意味着SR_Config表中不需要匹配记录，因此EXISTS始终返回true，因为它实际上是SELECT FROM v_cbi_Tracked_Tickets。 INNER JOIN意味着SR_Config表中有匹配的记录。



另一种方法是直接加入（使用LEFT）然后检查一个联接表中的关键字段为NULL：

Change your LEFT JOIN to INNER JOIN. LEFT JOIN means there does not need to be a matching record in the SR_Config table so EXISTS is always returning true because it is really SELECT FROM v_cbi_Tracked_Tickets. INNER JOIN will mean there is a matching record in the SR_Config table.

An alternative way to do it i would be to join directly (using LEFT) and then check a key field in the joined table for NULL:

SELECT CASE WHEN C.SR_Service_RecID IS NULL THEN 'NO' ELSE 'YES' END AS 'HasConfig',V.*
FROM v_cbi_Tracked_Tickets v
LEFT JOIN SR_Config C ON C.SR_Service_RecID = V.SR_Service_RecID

I不知道两种方法是否更有效，只是采用不同的方式做同样的事情。我相信第二种方法更有效，但不知道。

I don''t know if either approach is more efficient, just different ways of doing the same thing. I would believe the second approach is more efficient, but don''t know.

解决方案1由 ryanb31 [ ^ ]非常好，但我建议你阅读这些内容：

SQL连接 [ ^ ]

SQL联接的可视化表示 [ ^ ]

Solution 1 by ryanb31[^] is very good, but i would suggest you to read these:
SQL Joins[^]
Visual Representation of SQL Joins[^]

这篇关于TSQL编码问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！