For Example.:

Table 1 - Projects

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.

CodePudding user response：

;WITH MasterProjects as
(
SELECT 
Name, 
CASE 
    WHEN (SELECT COUNT(1) FROM Table1 t2 WHERE t2.Name LIKE t1.Name   '%' AND Active = 'Y') > 0 THEN 'Y' 
    ELSE 'N' END AS Active
FROM Table1 t1
WHERE MasterProject = ''
)
SELECT t1.Id, t1.Name, t1.MasterProject, t2.Active
FROM Table1 t1
JOIN MasterProjects t2 ON t1.Name LIKE t2.Name   '%'

I assume that isActive is not null for child projects

CodePudding user response：

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

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)

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: