SQL: selecting a value based on values from a number of rows in another table -

i have table networks each network can have multiple circuits. each network has over-all status (red/yellow/green) , each circuit has individual status (red/green). circuit's statuses each set manually. network's status such:

• if circuits green --> green
• if circuits red --> red
• if @ least 1, not all, circuits green --> yellow
• if there no circuits --> null(no status)

i trying select of networks statuses being determined dynamically select rather having save , manage status column in table. cannot figure out efficient way this. have works (both small tables, < 100, rows relatively static amount of data), wildly inefficient , i'm hoping there better way.

select (case      when (         select count(*)          network_circuits          network_id = n.network_id     ) = 0 'nostatus'     when (         select count(*)          network_circuits          network_id = n.network_id         , [status] = 'greenstatus'     ) = (         select count(*)          network_circuits          network_id = ssn.network_id     ) 'greenstatus'     when (         select count(*)          network_circuits          network_id = n.network_id         , [status] = 'redstatus'     ) = (         select count(*)          network_circuits          network_id = n.network_id     ) 'redstatus'     else 'yellowstatus' end) network_status networks n 

here's 1 way:

select     case          when circuitcount null      'nostatus'         when greencount = circuitcount 'greenstatus'         when greencount = 0            'redstatus'         else 'yellowstatus'     end network_status networks n left join (   select  count(*) circuitcount,             count(nullif([status],'redstatus')) greencount,             network_id        network_circuits     group network_id )   c    on n.network_id = c.network_id