Improve select rows from table1 in table2 but not in table3
Unless I'm misunderstanding your question, I would think a combination of EXISTS and NOT EXISTS would give you what you want.

--Setup
set nocount on
DECLARE @Code TABLE (CodeId INT)
DECLARE @Aggregation TABLE (AggregationId INT)
DECLARE @AggregationChildren TABLE (AggregationChildrenId INT)

INSERT INTO @Code (CodeId)
VALUES (1),(2)

INSERT INTO @Aggregation (AggregationId)
VALUES (1),(2)

INSERT INTO @AggregationChildren (AggregationChildrenId)
VALUES (1)

--The query
SELECT c.CodeId
FROM @Code c
WHERE EXISTS (
        SELECT *
        FROM @Aggregation
        WHERE AggregationId = c.codeid
        )
    AND NOT EXISTS (
        SELECT *
        FROM @AggregationChildren
        WHERE AggregationChildrenId = c.codeid
        )




| CodeId |
|--------|
| 2      |