Labels with a software asset.sql

-- Identified machines with a label in the Labs and Classrooms label group with a particular software title installed
-- Originally used to create web pages to help find classrooms that had software available
SELECT
DISTINCT(LABEL.NAME)
FROM ASSET
JOIN ASSET_DATA_6 on ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID
JOIN SOFTWARE_LABEL_JT on SOFTWARE_LABEL_JT.LABEL_ID = ASSET_DATA_6.FIELD_10001
JOIN SOFTWARE on SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
JOIN MACHINE on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden'
LEFT JOIN LABEL_LABEL_JT on LABEL_LABEL_JT.CHILD_LABEL_ID = LABEL.ID
LEFT JOIN LABEL PARENTLABEL on (PARENTLABEL.ID = LABEL_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')

WHERE ASSET.ID = 1332
AND PARENTLABEL.NAME = "Labs and Classrooms"
ORDER BY LABEL.NAME