expected_asset_list

Column	Type
asset_id	TEXT
host	TEXT
description	TEXT
asset_tag	TEXT
asset_type	TEXT
assignment	TEXT
boundaries
parent_boundaries

SQL DDL

CREATE VIEW expected_asset_list AS
SELECT 
    ast.asset_id,
    ast.name AS host,
    ast.description,
    ast.asset_tag,
    astyp.value AS asset_type,
    assignment.value AS assignment,
    GROUP_CONCAT(bnd.name, ', ') AS boundaries,
    GROUP_CONCAT(parent.name, ', ') AS parent_boundaries
FROM asset ast
INNER JOIN asset_boundary ab ON ab.asset_id = ast.asset_id
INNER JOIN boundary bnd ON bnd.boundary_id = ab.boundary_id
LEFT JOIN boundary parent ON parent.boundary_id = bnd.parent_boundary_id
INNER JOIN asset_type astyp ON astyp.asset_type_id = ast.asset_type_id
INNER JOIN assignment ON assignment.assignment_id = ast.assignment_id
WHERE ast.asset_tag = "ACTIVE"
GROUP BY ast.asset_id