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