连接表上的SQL Server 2000条件 [英] SQL Server 2000 condition on joined table

问题描述

以下是我尝试解决几天的问题的简化示例，它与我先前提出的问题有关，但是我增加了该问题的范围.

The following is a simplified example of something I've been trying to solve for a couple days and is related to a question I previously asked, but I have increased the problem scope in this question.

在此示例中，一个问题可以具有多个联系人，并且一个联系人可以具有多个角色.

In this example, an Issue can have multiple Contacts, and a Contact can have multiple Roles.

仅当至少一个联系人具有RoleID = 4的角色时，我才想显示结果.这意味着我要查看问题的所有联系人和角色，但是问题必须具有RoleID = 4的至少一个联系人.

I want to show results only if at least one Contact has a Role with RoleID = 4. This means I want to see all Contacts and Roles for an Issue, but the Issue must have at least one Contact where RoleID = 4.

SELECT 
    i.ID, i.Date, c.Name, r.RoleID, r.RoleName
FROM 
    Issue i
INNER JOIN
    Contact c ON c.IssueID = i.ID
INNER JOIN
    Role r ON r.ContactID = c.ID

如果重要的话，这是在SQL Server 2000上进行的.是的，我知道不再支持SQL Server 2000，使用寿命已过，危险和不负责任的使用等等.但是该服务器将在今年晚些时候由另一个部门进行升级，现在我无法控制它的使用.

If it matters, this is on SQL Server 2000. And, yes, I know that SQL Server 2000 is no longer supported, way past end of life, dangerous and irresponsible to use, etc, etc; but the server is due for upgrade by another department later in the year and it's use is beyond my control for now.

推荐答案

仅当至少一个联系人具有以下角色时，我才想显示结果 RoleID =4.这意味着我想查看一个联系人的所有联系人和角色. 问题，但是问题必须至少具有一个RoleID = 4的联系人.

I want to show results only if at least one Contact has a Role with RoleID = 4. This means I want to see all Contacts and Roles for an Issue, but the Issue must have at least one Contact where RoleID = 4.

我会使用EXISTS来过滤问题列表.

I would use EXISTS to filter the list of issues.

SELECT 
    i.ID, i.Date, c.Name, r.RoleID, r.RoleName
FROM 
    Issue i
INNER JOIN
    Contact c ON c.IssueID = i.ID
INNER JOIN
    Role r ON r.ContactID = c.ID
WHERE EXISTS (
    SELECT * 
    FROM Contact c1 ON c1.IssueID = i.ID
    INNER JOIN
    Role r1 ON r1.ContactID = c1.ID
    AND r1.RoleID = 4
)

不是判断您对SQL 2000的看法，但是....您有下车的计划吗?如今，下载免费的Express版本非常容易，甚至可以尝试升级甚至尝试使用免费的云解决方案

Not judging you on the SQL 2000 thing but.... do you have a plan to get off? It's pretty simple nowadays to download a free Express edition and have a go at upgrading or even try a free cloud solution

这篇关于连接表上的SQL Server 2000条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！