Add custom bool column where data is calculated based on values from linked entities

Question

I have 2 tables: Entity and EntityItem.

EntityItems table has a Reason column which is nullable enum.

I'm trying to write a view that would return some Entititys columns and additionally a boolean column that states whether all corresponding EntityItem.Reason have a non-null value.

The following query returns somewhat what I want:

SELECT EntityItem.Id, COUNT(EntityItem.Reason) As Test
FROM EntityItem
GROUP BY EntityItem.ParentEntityId
ORDER BY Test DESC

Example output:

Id             Test
132189         4
132190         2
132197         1
1              0
2              0
3              0
4              0
5              0
6              0

However, when I try to add this to a final query I get duplicated lines for each EntityItem

SELECT [Entity].[Id],
       ...
        (SELECT CASE WHEN (SELECT COUNT([EntityItem].[Reason]) FROM [EntityItem] WHERE [EntityItem].[ParentEntityId] = [Entity].[Id]) = 0
        THEN 0
        ELSE 1
        END) AS Test
FROM [Entity]
  ...
  LEFT JOIN [EntityItem] ON [Entity].[Id] = [EntityItem].[ParentEntityId]

Example output:

Id             Test
1              1
1              1
2              0
2              0
2              0
2              0
3              1
3              1
4              0

Question 1: Is my approach correct?

Question 2: Is there a way to remove duplicated lines without DISTINCT?

Answer 1

For your second query you need to aggregate before joining, for example by using outer apply something like:

select e.Id, 
  case when i.cnt = 0 then 0 else 1 end as Test
from Entity e
outer apply (
    select Count(Reason) cnt
    from EntityItem i
    where i.ParentEntityId = e.Id
)i;

Saying that, since you are always returning a value of 1 if the count is greater than zero you don't actually need to count anything:

select e.Id, 
  case when exists (
    select * from EntityItem i 
    where i.ParentEntityId = e.Id
  ) 
  then 1 else 0 end as Test
from Entity e;