DECLARE @table TABLE
(
ProposalId INT,
RiskId INT,
RiskTaken BIT
)
INSERT INTO @table
SELECT 1, 1, 1
UNION ALL SELECT 1,2,0
UNION ALL SELECT 1,3,0
UNION ALL SELECT 2,1,0
UNION ALL SELECT 2,2,0
UNION ALL SELECT 2,3,0
;
WITH ProposalRisk
AS
(SELECT DISTINCT ProposalId FROM @table WHERE RiskTaken = 1
)
SELECT
t.[ProposalId],
t.[RiskId],
t.[RiskTaken],
CASE
WHEN pr.ProposalId IS NOT NULL THEN 1
ELSE
0
END AS ProposalTaken
FROM
@table t
LEFT OUTER JOIN ProposalRisk pr ON t.ProposalId = pr.ProposalId
Looks like it should work