peter_boolean_view.sql

-- Table definitions
DROP TABLE IF EXISTS sensors;
DROP TABLE IF EXISTS projects;
DROP VIEW IF EXISTS projects_matrix_sensors;

CREATE TABLE projects (
    id        INTEGER PRIMARY KEY,
    name      CHAR(50)
);

CREATE TABLE sensors
(
    id        INTEGER PRIMARY KEY,
    name      CHAR(50),
    timelapse INTEGER, -- 0: false, 1: true

    project_id INTEGER,
    FOREIGN KEY(project_id) REFERENCES projects(id)
);

-- Test data
INSERT INTO projects (id, name)
VALUES (1, 'project 1'),
       (2, 'project 2'),
       (3, 'project 3'),
       (4, 'project 4');

INSERT INTO sensors (name, timelapse, project_id)
VALUES ('sensor timelapse project 1', 1, 1),
       ('sensor motion project 1', 0, 1),
       ('sensor unknown type project 1', NULL, 1),
       ('sensor timelapse project 2', 1, 2),
       ('sensor unknow type project 4', NULL, 4);

-- Intermediate view to keep things readable
CREATE VIEW projects_matrix_sensors AS
SELECT DISTINCT
       projects.name,

       CASE (SELECT COUNT(*) FROM sensors WHERE sensors.project_id = projects.id AND timelapse = 0)
         WHEN 0 THEN 'No'
         ELSE 'Yes'
       END 'project_has_motion_sensors',

       CASE (SELECT COUNT(*) FROM sensors WHERE sensors.project_id = projects.id AND timelapse = 1)
         WHEN 0 THEN 'No'
         ELSE 'Yes'
       END 'project_has_timelapse_sensors',

       CASE (SELECT COUNT(*) FROM sensors WHERE sensors.project_id = projects.id AND timelapse IS NULL)
         WHEN 0 THEN 'No'
         ELSE 'Yes'
       END 'project_has_unknown_sensors'

       FROM projects LEFT JOIN sensors s on projects.id = s.project_id;

-- Final query
SELECT
       name,
       CASE
        WHEN (project_has_motion_sensors LIKE 'Yes' AND project_has_timelapse_sensors LIKE 'Yes') THEN 'timelapse + motion triggered'
        WHEN (project_has_motion_sensors LIKE 'Yes' AND project_has_timelapse_sensors LIKE 'No') THEN 'motion triggered'
        WHEN (project_has_motion_sensors LIKE 'No' AND project_has_timelapse_sensors LIKE 'Yes') THEN 'timelapse'
           -- You might want to add more WHEN clauses for combinations including the "unknown" type sensors such as "timelapse + unknown", ...
           ELSE 'unknown'
       END sensor_types

FROM projects_matrix_sensors;