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.

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

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.

回答1:

You can get the issues using aggregation and having:

SELECT i.ID
FROM Issue i INNER JOIN
     Contact c
     ON c.IssueID = i.ID INNER JOIN
     Role r
     ON r.ContactID = c.ID
GROUP BY i.ID
HAVING SUM(CASE WHEN r.RoleId = 4 THEN 1 ELSE 0 END) > 0;

If you need the additional columns, you can join those back in.

回答2:

Another solution: Just add two more joins to same Contact / Role tables + DISTINCT to remove the duplicates if there are multiple contacts with RoleID = 4:

SELECT DISTINCT
    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
   INNER JOIN Сontact c2 ON c2.IssueID = i.ID
   INNER JOIN Role r2 ON r2.ContactID = c2.ID AND r2.RoleID = 4

回答3:

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.

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
)

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