I'd like to run reports by label but only for selected labels, so I made a label group. I can't figure out how to select that label group, though. I tried adding to the WHERE clause LABEL.NAME = 'mygroup' but I got no results. I would really prefer not to list my labels in an IN, since I would have to update reports when my label list changed. Any thoughts? Thanks!
--
Sean Porterfield
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
You can query on parent labels by performing a table join with LABEL_LABEL_JT. This table joins parent labels to child labels. Here is an example:


SELECT C.NAME AS 'Child Label Name' FROM LABEL P
LEFT JOIN LABEL_LABEL_JT LLJT ON (P.ID = LLJT.LABEL_ID)
LEFT JOIN LABEL C ON (C.ID = LLJT.CHILD_LABEL_ID)
WHERE P.NAME = 'Parent Label Group'
Answered 08/11/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I understand what you're saying, but I can't quite get it to work. Maybe if I spend a little more time looking at the raw data it will make more sense.

I started with an existing report:

select LABEL.NAME as LABEL_NAME, MACHINE.NAME as MACHINE_NAME, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, RAM_TOTAL,
TRIM('\n' from TRIM(PROCESSORS)) as PROCESSOR,
TRIM('\n' from TRIM(VIDEO_CONTROLLERS)) AS VIDEO
from MACHINE
LEFT JOIN MACHINE_LABEL_JT ON MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL ON LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE LABEL.TYPE !='hidden'
ORDER BY LABEL_NAME, MACHINE_NAME

LEFT JOIN MACHINE_LABEL_JT ON MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID matches the machine to a label.
LEFT JOIN LABEL ON LABEL.ID = MACHINE_LABEL_JT.LABEL_ID matches the label to get the name.

I suspect I need LEFT JOIN LABEL_LABEL_JT LLJT ON (LABEL.ID = LLJT.CHILD_LABEL_ID) to get the parent ID for that child and then back to LABEL again to get the name for the WHERE clause.
I tried LEFT JOIN LABEL P ON P.ID = LLJT.ID but I get mysql error: [1054: Unknown column 'LLJT.ID' in 'on clause']
Answered 08/11/2010 by: sporterfield
Senior Yellow Belt

Please log in to comment
0
Post the query you've got written that doesn't work (the one you refer to in your first post) and I'll fix it for you.
Answered 08/11/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
This is as close as I've gotten:

select LABEL.NAME as LABEL_NAME, MACHINE.NAME as MACHINE_NAME, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, RAM_TOTAL,
TRIM('\n' from TRIM(PROCESSORS)) as PROCESSOR,
TRIM('\n' from TRIM(VIDEO_CONTROLLERS)) AS VIDEO
from MACHINE
LEFT JOIN MACHINE_LABEL_JT ON MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL ON LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
LEFT JOIN LABEL_LABEL_JT LLJT ON (LABEL.ID = LLJT.CHILD_LABEL_ID)
LEFT JOIN LABEL P ON P.ID = LLJT.ID
WHERE LABEL.TYPE !='hidden'
ORDER BY LABEL_NAME, MACHINE_NAME
Answered 08/11/2010 by: sporterfield
Senior Yellow Belt

Please log in to comment
0
What is the name of the label group? The query you've posted only has the stipulation that the label is not hidden in the WHERE clause. We need to specify the label group.
Answered 08/11/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Oops, too much copy/paste in my testing. I'm trying to select PARENT_LABEL.NAME = 'Regions' - the !='hidden' was there in the original query.
Answered 08/11/2010 by: sporterfield
Senior Yellow Belt

Please log in to comment
0

select C.NAME as LABEL_NAME, MACHINE.NAME as MACHINE_NAME, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, RAM_TOTAL,
TRIM('\n' from TRIM(PROCESSORS)) as PROCESSOR,
TRIM('\n' from TRIM(VIDEO_CONTROLLERS)) AS VIDEO
from MACHINE
LEFT JOIN MACHINE_LABEL_JT MLJT ON (MACHINE.ID = MLJT.MACHINE_ID)
LEFT JOIN LABEL C ON (C.ID = MLJT.LABEL_ID)
LEFT JOIN LABEL_LABEL_JT LLJT ON (C.ID = LLJT.CHILD_LABEL_ID)
LEFT JOIN LABEL P ON (P.ID = LLJT.LABEL_ID)
WHERE P.NAME = 'Regions' AND C.TYPE !='hidden'
ORDER BY LABEL_NAME, MACHINE_NAME
Answered 08/11/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
By Jove, I think you've got it! I have to wait for my labels to update to be sure, but initial query returned expected results. Thank you so much!
Answered 08/11/2010 by: sporterfield
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity