The impossible

report.sql

SELECT
academy.name as academy,
GROUP_CONCAT(DISTINCT competency.name SEPARATOR ', ') as competency,
core_lo.name as lo,
co.name as co,
csfv.value as proficiency,
lo_non.name as non_core_lo,
other_co.name as other_cos

FROM learning_objective_group academy
JOIN learning_objective_group_relation academy_r ON academy_r.learning_objective_group_id = academy.id AND academy_r.delete_date IS NULL
JOIN learning_objective core_lo ON core_lo.id = academy_r.learning_objective_id AND core_lo.delete_date IS NULL
/* competency? */
LEFT JOIN (
    SELECT competency.name, competency.id, core.id as lo_id
    FROM learning_objective_group_relation r
    JOIN learning_objective_group competency ON competency.id = r.learning_objective_group_id  AND competency.delete_date IS NULL
    JOIN learning_objective core ON core.id = r.learning_objective_id
    WHERE r.delete_date IS NULL

) as competency ON competency.id != academy.id AND competency.lo_id = core_lo.id

/* Content objects & proficiency*/
LEFT JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = core_lo.id AND co_lo.delete_date IS NULL
LEFT JOIN content_object co ON co.id = co_lo.content_object_id AND co.delete_date IS NULL
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND csfv.customer_specific_field_id = UNHEX("DB802EA9AED711E99B6602313DCACE58") AND csfv.delete_date IS NULL

/* NON core LO */
LEFT JOIN content_object_learning_objective co_lo2 ON co_lo2.learning_objective_id != core_lo.id AND co_lo2.content_object_id = co.id AND co_lo2.delete_date IS NULL
LEFT JOIN learning_objective as lo_non ON lo_non.id = co_lo2.learning_objective_id AND lo_non.name NOT IN('Envision','Engage','Empower','Enrich')

# Other cos having this los
LEFT JOIN content_object as other_co ON other_co.id != co.id AND other_co.id = co_lo.content_object_id AND other_co.delete_date IS NULL

WHERE academy.name REGEXP '^(Envision|Engage|Empower|Enrich)' AND academy.delete_date IS NULL
GROUP BY lo, non_core_lo
ORDER BY academy, competency, proficiency