SELECT CVTP.subject_id, CVT.name, CVT2.name, CVTP.object_id, CVT3.name, CVTP.pathdistance, count(F.name) FROM {cvtermpath} CVTP INNER JOIN CVTerm CVT ON CVTP.subject_id = CVT.cvterm_id INNER JOIN CVTerm CVT2 on CVTP.type_id = CVT2.cvterm_id INNER JOIN CVTerm CVT3 on CVTP.object_id = CVT3.cvterm_id INNER JOIN Feature_cvterm FCVT on FCVT.cvterm_id = CVT.cvterm_id INNER JOIN Feature F on FCVT.feature_id = F.feature_id WHERE object_id = 7438 GROUP BY CVTP.subject_id, CVT.name, CVT2.name, CVTP.object_id, CVT3.name, CVTP.pathdistance ORDER BY pathdistance // THE MATERIALIZED VIEW!!! SELECT CVT3.name, CVTP.object_id, count(F.name) FROM {cvtermpath} CVTP INNER JOIN CVTerm CVT ON CVTP.subject_id = CVT.cvterm_id INNER JOIN CVTerm CVT2 on CVTP.type_id = CVT2.cvterm_id INNER JOIN CVTerm CVT3 on CVTP.object_id = CVT3.cvterm_id INNER JOIN Feature_cvterm FCVT on FCVT.cvterm_id = CVT.cvterm_id INNER JOIN Feature F on FCVT.feature_id = F.feature_id INNER JOIN CV on CV.cv_id = CVT.cv_id WHERE CV.name = 'biological_process' or CV.name = 'molecular_function' or CV.name = 'cellular_component' GROUP BY CVT3.name, CVTP.object_id