SQL:根据另一个表中的许多行中的值选择一个值

I have a table NETWORKS where each network can have multiple CIRCUITS. Each network has an over-all status (red/yellow/green) and each circuit has an individual status (red/green). The circuit's statuses are each set manually. The network's status is as such:I have a table NETWORKS where each network can