This is a case of premature exclusion, nothing to worry about 😀
USE tempdb;
GO
DECLARE @inspection TABLE
(
INSPNO INT NULL
,StartDateDATE NULL
,CompDateDATE NULL
,Status NVARCHAR(50) NULL
);
INSERT INTO @inspection(INSPNO,StartDate,CompDate,Status)
VALUES
(123, '3/30/14','3/30/14','Passed')
,(124, '3/31/14','3/31/14','Passed');
DECLARE @INSPECTIONCONTACT TABLE
(
ConactNo INT NULL
,INSPNO INT NULL
,FirstName NVARCHAR(50)
,LastName NVARCHAR(50)
,PhoneNumber NVARCHAR(50)
,Capacity NVARCHAR(50)
);
INSERT INTO @INSPECTIONCONTACT
(
ConactNo
,INSPNO
,FirstName
,LastName
,PhoneNumber
,Capacity
)
VALUES
(1000, 123, 'John', 'Doe', '555-555-5555','Owner')
,(1001, 123, 'Jane', 'Doe', '555-555-5555','Owner')
,(1002, 123, 'Jane', 'Handy','555-555-5555','Contractor')
,(1003, 124, 'Fred', 'Smith','555-555-5555','Contractor');
SELECT
X.INSPNO
,X.FIRSTNAME
,X.LASTNAME
FROM
(
SELECT
INS.INSPNO
,ISC.FIRSTNAME
,ISC.LASTNAME
,CASE
WHEN SUM
(
CASE
WHEN ISC.Capacity = N'Contractor' THEN 0
ELSE 1
END ) OVER
(
PARTITION BY INS.INSPNO
) > 0 AND ISC.Capacity <> N'Contractor' THEN 1
WHEN SUM
(
CASE
WHEN ISC.Capacity = N'Contractor' THEN 0
ELSE 1
END ) OVER
(
PARTITION BY INS.INSPNO
) = 0 AND ISC.Capacity = N'Contractor' THEN 1
ELSE 0
END AS SHOW_CONT
FROM @inspection INS
INNER JOIN @INSPECTIONCONTACT ISC
ON INS.INSPNO = ISC.INSPNO
) AS X
WHERE X.SHOW_CONT = 1;