KACE Product Support

This would work.

SELECT DISTINCT M.NAME, IP,

GROUP_CONCAT(DISTINCT L.NAME SEPARATOR '\n') AS LABEL_NAME

FROM MACHINE M

LEFT JOIN MACHINE_LABEL_JT ON M.ID = MACHINE_LABEL_JT.MACHINE_ID

LEFT JOIN LABEL L ON MACHINE_LABEL_JT.LABEL_ID = L.ID

GROUP BY M.NAME

ORDER BY M.NAME

You can also restrict the labels that are returned by using a WHERE statement.  For example, I have a bunch of location labels (Pittsburgh computers, Marelan computers, etc) and to return just those labels, I would tweak dgullett's query like this so that only the "computers" labels would be listed:

SELECT DISTINCT M.NAME, IP,
L.NAME AS LABEL_NAME
FROM MACHINE M
LEFT JOIN MACHINE_LABEL_JT ON M.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL L ON MACHINE_LABEL_JT.LABEL_ID = L.ID
WHERE L.NAME rlike 'computers'
GROUP BY M.NAME
ORDER BY M.NAME

If your goal is truly to return *all* of your labels, then dgullett's approach works great.

John

Another trick is to only select labels that are in a label group. We use LDAP labels for department and all of those are in the Departments label group (which has an ID of 258). The following query just includes labels in that group:

 SELECT IP,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS DEPARTMENT,
MACHINE.NAME AS SYSTEM_NAME,MACHINE.USER_NAME,USER_LOGGED,USER_FULLNAME
FROM MACHINE  
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 
LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID 
LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID 
WHERE LABEL_LABEL_JT.LABEL_ID = 258
GROUP BY MACHINE.ID
ORDER BY SYSTEM_NAME

The label group is specified as part of the LABEL_LABEL_JT join statement.