ITNinja

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'


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']

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.

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

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.

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.


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


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!