Last active
December 10, 2019 15:08
-
-
Save sroehrl/af44d2e9760bc4e9c1ee7289dae883ac to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
lo.id, | |
"Envision" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'Envision' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'Envision' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Empower" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'Empower' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'Empower' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Engage" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'Engage' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'Engage' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Enrich – T&C" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'Tender & Contracting' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'Tender & Contracting' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Enrich – Pricing" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'Pricing Model' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'Pricing Model' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Enrich – ChM Channel Marketing" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'ChM Channel Marketing' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'ChM Channel Marketing' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Enrich – ChM KAM" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'ChM KAM' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'ChM KAM' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
UNION | |
SELECT | |
lo.id, | |
"Enrich – ChM Retail" as academy, | |
competency.competency, | |
lo.name, | |
csfv.value, | |
co.id as co_id, | |
co.name as co_name | |
FROM learning_objective lo | |
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = ( | |
SELECT id FROM tag g WHERE g.name = 'ChM Retail' AND g.delete_date IS NULL | |
) | |
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL | |
JOIN ( | |
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag | |
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL | |
WHERE tag.name != 'ChM Retail' | |
GROUP BY sr.learning_objective_id | |
) as competency ON competency.learning_objective_id = lo.id | |
LEFT JOIN content_object co on co.id = co_lo.content_object_id | |
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND ( | |
csfv.value = 'Fundamental' OR | |
csfv.value = 'Intermediate' OR | |
csfv.value = 'Advanced' | |
) | |
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL | |
ORDER BY academy, competency |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment