Question 89861 in PostgreSQL Question posted in
The official documentation can be found here.

How to count if there does not exist TRUE in the same category? – Postgresql

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    Don’t join, don’t aggregate, just use NOT IN or NOT EXISTS in order to find roads that don’t have a working camera:

    select *
from road
where roadnum not in (select roadnum from cameras where isworking);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search