I’m struggling with what I believe should be a requirement not hard to implement. Is is possible in MS SQL to do something similar to this simplified example with one select?

declare @t1 table (id int, name varchar(60))
declare @t2 table (id int, name varchar(60))
declare @t3 table (id int, name varchar(60))
declare @t4 table (t1id int, t2id int, t3id int, id int, name varchar(60))

insert into @t1 values 
    (1, 't1_1'),
    (2, 't1_2')
insert into @t2 values
    (1, 't2_1'),
    (2, 't2_2'),
    (3, 't2_3')
insert into @t3 values
    (1, 't3_1'),
    (2, 't3_2'),
    (3, 't3_3'),
    (4, 't3_4')
insert into @t4 values
    (2, 1, 4, 1, 't4_1'),
    (3, 2, 1, 2, 't4_2'),
    (1, 1, 1, 3, 't4_3'),
    (2, 2, 2, 4, 't4_4'),
    (3, 3, 3, 5, 't4_5'),
    (1, 2, 3, 6, 't4_6'),
    (1, 3, 1, 7, 't4_7'),
    (1, 2, 1, 8, 't4_8'),
    (1, 2, 4, 9, 't4_9'),
    (2, 2, 3, 10, 't4_10')

select base.*,
( -- https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16#c-specifying-multiple-values-as-a-derived-table-in-a-from-clause
    select count(distinct v.name3)
    from
    (
        values (base.name1, base.name2, base.name3)
    ) as v (name1, name2, name3)
    group by v.name1, v.name2
) as [ShowCountOfName3PerName1&Name2OnEveryRow]
from
(
    select t1.name as name1,
    t2.name as name2,
    t3.name as name3,
    t4.name as name4
    from @t4 as t4
    inner join @t1 as t1
    on t4.t1id = t1.id
    inner join @t2 as t2
    on t4.t2id = t2.id
    inner join @t3 as t3
    on t4.t3id = t3.id
) as base
order by base.name1, base.name2, base.name3, base.name4

Unfortunately, this query displays only the digit 1 on every row:

I tried it this way:

select base.*,
cnt.[ShowCountOfName3PerName1&Name2OnEveryRow]
from
(
    select t1.name as name1,
    t2.name as name2,
    t3.name as name3,
    t4.name as name4
    from @t4 as t4
    inner join @t1 as t1
    on t4.t1id = t1.id
    inner join @t2 as t2
    on t4.t2id = t2.id
    inner join @t3 as t3
    on t4.t3id = t3.id
) as base
outer apply
(
    select count(distinct base.name3) as [ShowCountOfName3PerName1&Name2OnEveryRow]
    from base
    group by base.name1, base.name2
) as cnt
order by base.name1, base.name2, base.name3, base.name4

The result is Invalid object name ‘base’.
I can imagine this as a stored procedure where I insert the result into declare @result table(…) and then update @result set…, but this way it will have to be a stored procedure and to enumerate a result set of over 100k rows and update all of them every time. While I need this to be a view to be able to query it with Entity Framework in a more efficient way. Any hints will be greatly appreciated!