Multi-conditions data filtering involving BOOLEAN columns-CodePudding

I am lost in trying to filter data from a table containing BOOLEAN columns, where the filtering conditions apply some further consideration on one of the BOOLEAN columns.

I explain my question with example below, reproduced in this db-fiddle.

CREATE TABLE trip_segments(trip_id INT,segment_id INT,trip_timestamp TIMESTAMP, 
  trip_distance DOUBLE PRECISION,  foot BOOLEAN,bike BOOLEAN, bus BOOLEAN, car BOOLEAN)

INSERT INTO trip_segments(trip_id, segment_id, trip_timestamp, trip_distance,
  foot, bike, bus, car)
VALUES  (535521,2,'2016-04-08 13:56:41 01',9008.23,false,false,false,true),
 (536625,8,'2016-04-11 17:38:02 01',9821.37,true,false,false,false),
 (536624,1,'2016-04-11 14:23:20 01',1872.41,true,false,false,false),
 (537733,3,'2016-04-13 13:19:49 01',14967.96,true,false,true,false),
 (536623,0,'2016-04-11 10:08:32 01',8244.27,false,false,true,false), 
 (544627,5,'2016-04-25 11:24:37 01',3648.63,false,true,false,false)

So in this table, I want retrieve rows that satisfy the following conditions:

rows in which ONLY one of foot, bike, bus or car is TRUE.
further, foot trips should only be selected if trip_distance < 5000 (foot trip over 5,000m is deviant).
trips covered between 2016-4-07 and 2106-4-15 only.

My query below didn't return expected result:

SELECT *
FROM trip_segments
WHERE foot 
  AND trip_distance < 5000
AND trip_timestamp BETWEEN '2016-04-07' AND '2016-04-15'

trip_id segment_id  trip_timestamp    trip_distance foot    bike    bus car
536624      1      2016-04-11 14:23:20  1872.41      t        f      f   f

Expected output:

trip_id segment_id    trip_timestamp    trip_distance   foot    bike    bus car
535521      2      2016-04-08 13:56:41     9008.23        f       f      f   t
536624      1      2016-04-11 14:23:20     1872.41        t       f      f   f
536623      0      2016-04-11 10:08:32     8244.27        f       f      t   f

Here is the dbfiddle.

CodePudding user response：

Your condition can be written as:

SELECT *
FROM trip_segments
WHERE (
   (foot and not bike and not bus and not car AND trip_distance < 5000)
   OR (not foot and bike and not bus and not car)
   OR (not foot and not bike and bus and not car)
   OR (not foot and not bike and not bus and car)
  )
AND trip_timestamp BETWEEN '2016-04-07' AND '2016-04-15'

See db<>fiddle.

CodePudding user response：

Add an additional WHERE condition:

AND foot::integer   bus::integer   bike::integer   car::integer = 1