I'm not sure if this is what you're looking for.

DECLARE @Table1 TABLE(

Id char(1))

INSERT @Table1 VALUES('A'), ('B')

DECLARE @Table2 TABLE(

Id char(1),

Value char(1))

INSERT @Table2 VALUES('A', 'X'),('A', 'Y'),('A', 'Z'),('B', 'Y'),('B', 'Z')

DECLARE @ExcludedValue char(1) = 'X'

--Option 1

SELECT t1.Id

FROM @Table1 t1

WHERE NOT EXISTS(SELECT 1

FROM @Table2 t2

WHERE t1.Id = t2.Id

AND t2.Value = @ExcludedValue)

--Option 2

SELECT t1.Id

FROM @Table1 t1

JOIN @Table2 t2 ON t1.Id = t2.Id

GROUP BY t1.Id

HAVING MAX( CASE WHEN Value = @ExcludedValue THEN Value END) IS NULL

-- Option 3

SELECT t1.Id

FROM @Table1 t1

WHERE EXISTS(SELECT 1

FROM @Table2 t2

WHERE t1.Id = t2.Id

GROUP BY t2.Id

HAVING MAX( CASE WHEN t2.Value = @ExcludedValue THEN t2.Value END) IS NULL)