In SQL when creating temp tables, how can I set all boolean values to TRUE if even one related record is TRUE but others are FALSE

For Example.:

Table 1 – Projects

Id	Name	Master Project	Active
1	Project A		N
2	Project A – 1	1	Y
3	Project A – 2	1	N
4	Project B		N
5	Project B -1	4	N
6	Project C		NULL
7	Project C – 1	6	Y

I would like to return |Project | Active | |—-|—-| |Project A | Y| |Project B | N| |Project C | Y|

How would I write this in SQL so that it basically…

IF any project is active (master or child) then all are active IF all projects are not active, then not active If the master project is NULL check if any projects related are Active. Only returning one record back.

I was thinking this would include an exist subquery but not exactly sure how to construct it.

Answer

Here is my solution by using inner join table with sum(case when)

id	name	master_project	active
1	Project A		N
2	Project A – 1	1	Y
3	Project A – 2	1	N
4	Project B		N
5	Project B – 1	4	N
6	Project C
7	Project C – 1	6	Y

Join Table:

select *
from projects tb1
inner join projects tb2 
on tb1.id = tb2.master_project or (tb1.master_project is null and tb1.id = tb2.id)

id	name	master_project	active	id	name	master_project	active
1	Project A		N	1	Project A
1	Project A		N	2	Project A – 1	1	Y
1	Project A		N	3	Project A – 2	1	N
4	Project B		N	4	Project B		N
4	Project B		N	5	Project B – 1	4	N
6	Project C			6	Project C
6	Project C			7	Project C – 1	6	Y

Here is final query:

select tb1.name Project, case when sum(case when tb2.active = 'Y' then 1 end ) >= 1 then 'Y' else 'N' end Active
from projects tb1
inner join projects tb2 on tb1.id = tb2.master_project or (tb1.master_project is null and tb1.id = tb2.id)
group by tb1.name

Result:

Project	Active
Project A	Y
Project B	N
Project C	Y