CO, Contact, Core LO

report.sql

SELECT
    co.id AS co_id,
    co.name AS Content_Object,
    contact.value AS Client_Contact,
    lo.name AS core_lo

FROM content_object_customer_specific_field_value AS contact
JOIN content_object AS co ON contact.content_object_id = co.id
    AND HEX(contact.customer_specific_field_id) LIKE "%0FF6E9CDC81411E99B6602313DCACE58%"

    AND co.version=(
        SELECT MAX(co2.version)
        FROM content_object co2
        WHERE (co2.parent_id = co.id OR co2.id = co.id) AND co2.delete_date IS NULL
    )
/*CORE lo*/
LEFT JOIN content_object_learning_objective colo on colo.content_object_id = co.id AND colo.delete_date IS NULL
LEFT JOIN learning_objective lo on lo.id = colo.learning_objective_id AND lo.id IN(
    SELECT logr.learning_objective_id FROM learning_objective_group log
    JOIN learning_objective_group_relation logr on logr.learning_objective_group_id = log.id
    WHERE log.name IN('Envision', 'Engage', 'Empower', 'Enrich')
    )
WHERE lo.name IS NOT NULL
ORDER BY Client_Contact ASC, Content_Object ASC