How to count if there does not exist TRUE in the same category?-CodePudding

Assume I have two tables:

cameraNum|roadNum|isWorking
100      | 1     | TRUE
101      | 1     | FALSE
102      | 1     | TRUE
103      | 3     | FALSE
104      | 3     | FALSE
105      | 7     | TRUE
106      | 7     | TRUE
107      | 7     | TRUE
108      | 9     | FALSE
109      | 9     | FALSE
110      | 9     | FALSE

roadNum  | length
1        | 90
3        | 140
7        | 110 
9        | 209

I want to select a table like this: If there is no camera working, I put it in the table.

roadNum|length
3      | 140  
9      | 209

I tried this below:

SELECT r.roadNum, r.length
FROM Cameras c, Road r
WHERE c.isWorking = FALSE
AND h.highwayNum = c.highwayNum

But these code only fliter there exists FALSE in isWorking.

roadNum|length
1      | 90
3      | 140  
9      | 209

CodePudding user response：

You want roads whose all cameras are not working. Here is one way to do it with aggregation and having:

select r.*
from road r
inner join camera c on c.roadNum = r.roadNum
group by r.roadNum
having not bool_or(isWorking)

Demo on DB Fiddle

roadnum	length
3	140
9	209

CodePudding user response：

Regarding using not exists, yes, you can use it. The following uses a CTE to get only the roadnum of those satisfying the camera requirement then joins that to road: (see demo)

with no_working_caMera (roadnum) as 
     ( select distinct on (c1.roadNum) 
              c1.roadnum 
         from cameras c1
        where not c1.isworking
          and not exists (select null 
                            from  cameras c2
                           where c2.roadNum = c1.roadNum
                             and c2.isworking
                         ) 
         order by c1.roadnum
     )
select r.* 
  from no_working_camera nwc 
  join road r 
    on nwc.roadnum = r.roadnum;