All,

I have created a report which is return all labels beginning with HSD however if those entries have additional labels they are included as well. The report is finding the correct records however I do not want the other labels displayed in the report. I created the initial report using the wizard however I'm guessing the SQL query requires refinement.

The SQL query I am using is below if anyone can assist.

 

SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP, OS_NAME, (SELECT group_concat(distinct if(LABEL.NAME not like 'HDN_LABEL_%', LABEL.NAME, 'System Hidden') separator '\n') FROM MACHINE_LABEL_JT MLJT INNER JOIN LABEL ON MLJT.LABEL_ID = LABEL.ID WHERE MACHINE.ID = MLJT.MACHINE_ID ORDER BY LABEL.NAME) as LABEL_NAME  FROM MACHINE  WHERE ((( exists  (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and LABEL.NAME like 'HSD%')) ))  ORDER BY LABEL_NAME desc

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share