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