Find the amount of missing attributes in an EVA design.... (remove the subquery from this sql join)

Question

How would you remove the subquery within [Count of Missing Attributes] from the following example.

    --Setup data
    Declare @Attributes table (AttributeID int)
    Declare @Objects table (ObjectID int)
    Declare @ObjectAttributes table (ObjectID int, AttributeID int, val int)

    Insert Into @Objects (ObjectID) values (1)
    Insert Into @Objects (ObjectID) values (2)

    Insert Into @Attributes (AttributeID) values (1)
    Insert Into @Attributes (AttributeID) values (2)

    Insert Into @ObjectAttributes (ObjectID,AttributeID) values (1,1)
    Insert Into @ObjectAttributes (ObjectID,AttributeID) values (2,1)
    Insert Into @ObjectAttributes (ObjectID,AttributeID) values (2,2)

    --Query
    Select 
        ObjectID, 
        (Select Count(Distinct a2.AttributeID) FROM @Attributes a2) As [Total Count Of Attributes],
        Count(Distinct a.AttributeID) as [Count of attributes for this object],
        (Select Count(Distinct a2.AttributeID) FROM @Attributes a2) - Count(Distinct a.AttributeID) as [Count of Missing Attributes]
    FROM @Attributes a
    INNER Join @ObjectAttributes oa
        On oa.AttributeID = a.AttributeID 
    Group By ObjectID
    Order By ObjectID

Answer 1

This assumes no duplicate data is present. Remove the HAVING clause to include objects with no attributes (these are left out in the original query).

SELECT
   ob.Objectid
  ,count(at.Attributeid) [Total Count Of Attributes]      
  ,count(oa.Attributeid) [Count of attributes for this object]
  ,sum(case when oa.AttributeId is null then 1 else 0 end) [Count of Missing Attributes]
 from @Attributes at
  cross join @Objects ob
  left outer join @ObjectAttributes oa
   on oa.ObjectId = ob.ObjectId
    and oa.AttributeId = at.AttributeId
 group by ob.Objectid
 having count(distinct oa.Attributeid) > 0