/build/static/layout/Breadcrumb_cap_w.png
02/21/2017 982 views
Hi all,

I have an existing report that allows me to see inventory PCs older than 4 years. 

SELECT MACHINE.NAME, CS_MODEL, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, USER_FULLNAME, USER_LOGGED,  MACHINE.OS_NAME
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG

WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
HAVING DA.SHIP_DATE < DATE_SUB(NOW(), INTERVAL 4 YEAR)
ORDER BY DA.SHIP_DATE, MACHINE.NAME

This works perfectly, but now I need to modify the report so the results only shows those PC that are members of a specific label (e.g. 'staff'). Does anyone know how to implement this in the report above? I've done some research and tried various things on my own, but my SQL knowledge isn't great and I can't seem to get it. 

Thanks for any help.
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
You need to add two join statements to the MACHINE_LABEL_JT and then to the LABEL tables:
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID

Place those after the join to the DELL_ASSET table.

You can now add a statement to the where clause for the specific label:
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
AND LABEL.NAME = "staff"

Answered 02/22/2017 by: chucksteel
Red Belt

  • That's fantastic, thanks so much. It works great!

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share