Find all buildings having a door whose gml:name equals to ‘west door’ , and the door is associated with a wall surface whose gml:name equals to ‘west wall’

XML Query

<query xmlns="http://www.3dcitydb.org/importer-exporter/config">
  <typeNames>
    <typeName xmlns:bldg="http://www.opengis.net/citygml/building/2.0">bldg:Building</typeName>
  </typeNames>
  <filter>
    <propertyIsEqualTo>
      <valueReference>bldg:boundedBy/bldg:WallSurface[@gml:id='west wall']/bldg:opening/bldg:Door/gml:name</valueReference>
      <literal>west door</literal>
    </propertyIsEqualTo>
  </filter>
</query>

SQL Query for 3DCityDB v4 (PostgreSQL)

select
  distinct b.id,
  b.objectclass_id,
  b.gmlid
from
  citydb.building a
  inner join citydb.cityobject b on a.id = b.id
  inner join citydb.thematic_surface c on (
    a.id = c.building_id
    and c.objectclass_id = 34
  )
  inner join citydb.cityobject d on c.id = d.id
  inner join citydb.opening_to_them_surface e on c.id = e.thematic_surface_id
  inner join citydb.opening f on (
    e.opening_id = f.id
    and f.objectclass_id = 39
  )
  inner join citydb.cityobject g on f.id = g.id
where
  b.objectclass_id = 26
  and d.name = 'Outer Wall 1 (West)'
  and g.name = 'Door West'

SQL Query for 3DCityDB v5 (PostgreSQL)

SELECT bldg.id
  ,bldg.objectclass_id
  ,bldg.gmlid
FROM feature AS ftr
INNER JOIN feature_relation AS ftr_rlt_wall
  ON ftr_rlt_wall.from_feature = ftr.id
    AND ftr_rlt_wall.namespace = 'con'
    AND ftr_rlt_wall.name = 'Boundary'
INNER JOIN feature AS wall
  ON ftr_rlt_wall.to_feature = wall.id
    AND wall.objectclass_id = 34
INNER JOIN feature_relation AS ftr_rlt_bldg
  --Since relation is association, following expression can be also used.
  --ON (ftr_rlt_bldg.to_feature = wall.id OR ftr_rlt_bldg.from_feature = wall.id)
  ON ftr_rlt_bldg.to_feature = wall.id
    AND ftr_rlt_bldg.namespace = 'core'
    AND ftr_rlt_bldg.name = 'boundary'
    --AND ftr_rlt_bldg.relationtype = 'association'
INNER JOIN feature AS bldg
  ON ftr_rlt_bldg.from_feature = bldg.id
    AND bldg.objectclass_id = 26
WHERE 
  ftr.identifier  = 'Door West'
  AND ftr.objectclass_id = 39