SQL Query using grouped labels
Hi,
I am really new to SQL queries and am trying to get a list of all my systems that will be three years old at December 31, 2013. I need to group it by the smart labels I created under the smart label group "Locations". Would anyone be able to offer some guidance? Thanks
SELECT M.NAME AS COMPUTER_NAME,DA.SERVICE_TAG, M.CS_MODEL AS MODEL, M.CHASSIS_TYPE AS CHASSIS_TYPE, DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, LABEL.NAME
FROM LABEL, DELL_ASSET DA
LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER=DA.SERVICE_TAG
WHERE DA.SHIP_DATE < ('2013-12-31' - INTERVAL 3 YEAR)
ORDER BY CHASSIS_TYPE
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Since you are selecting from multiple tables you need to create a relationship between the two. When I tried that by adding a statement to the where clause the query was very slow, so instead I used a join to the DELL_ASSET table: # Select Columns to gather information from SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET # Gathering information from the below Tables FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130) LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID LEFT JOIN DELL_ASSET DA on DA.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER WHERE LABEL_LABEL_JT.LABEL_ID = 130 # 3 years old at date entered below 2013-12-31 AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR) GROUP BY MACHINE.ID # Ordered by Subnet smart labels ORDER BY LABEL.NAME
Since you are selecting from multiple tables you need to create a relationship between the two. When I tried that by adding a statement to the where clause the query was very slow, so instead I used a join to the DELL_ASSET table: # Select Columns to gather information from SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET # Gathering information from the below Tables FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130) LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID LEFT JOIN DELL_ASSET DA on DA.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER WHERE LABEL_LABEL_JT.LABEL_ID = 130 # 3 years old at date entered below 2013-12-31 AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR) GROUP BY MACHINE.ID # Ordered by Subnet smart labels ORDER BY LABEL.NAME
Please log in to answer
Posted by:
dugullett
11 years ago
Change to match your label names.
SELECT M.NAME AS COMPUTER_NAME,DA.SERVICE_TAG, M.CS_MODEL AS MODEL, M.CHASSIS_TYPE AS CHASSIS_TYPE, DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, LABEL.NAME
FROM LABEL, DELL_ASSET DA
LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER=DA.SERVICE_TAG
WHERE DA.SHIP_DATE < ('2013-12-31' - INTERVAL 3 YEAR)
AND LABEL.NAME RLIKE 'LABEL_1|LABEL_2|LABEL_3'
ORDER BY CHASSIS_TYPE
Posted by:
chucksteel
11 years ago
Here's a query that returns computers that have a label in my "Departments" group:
SELECT IP, GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS DEPARTMENT, MACHINE.NAME AS SYSTEM_NAME,MACHINE.USER_NAME,USER_LOGGED,USER_FULLNAME FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258) LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID WHERE LABEL_LABEL_JT.LABEL_ID = 258 GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
The important bits are the LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)
and WHERE LABEL_LABEL_JT.LABEL_ID = 258
In both cases you'll need to change the 258 to the ID of the label group that you're looking to filter on. You can add your statement for the ship date after the WHERE LABEL_LABEL_JT.LABEL_ID = 258 and before the GROUP BY statement.
Comments:
-
That has gotten me started, but my SHIP_DATE is not reporting properly now. Here is what I have now.
# Select Columns to gather information from
SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET
# Gathering information from the below Tables
FROM DELL_ASSET AS DA, MACHINE
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130)
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID
WHERE LABEL_LABEL_JT.LABEL_ID = 130
# 3 years old at date entered below 2013-12-31
AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR)
GROUP BY MACHINE.ID
# Ordered by Subnet smart labels
ORDER BY LABEL.NAME
I am getting equipment from last year in my query, not sure why. - joe.pyrczak 11 years ago-
Since you are selecting from multiple tables you need to create a relationship between the two. When I tried that by adding a statement to the where clause the query was very slow, so instead I used a join to the DELL_ASSET table:
# Select Columns to gather information from
SELECT MACHINE.NAME AS SYSTEM_NAME, BIOS_SERIAL_NUMBER, DA.SHIP_DATE,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR ',') AS SUBNET
# Gathering information from the below Tables
FROM MACHINE
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden')
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 130)
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID
LEFT JOIN DELL_ASSET DA on DA.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER
WHERE LABEL_LABEL_JT.LABEL_ID = 130
# 3 years old at date entered below 2013-12-31
AND DA.SHIP_DATE < DATE('2013-12-31' - INTERVAL '3' YEAR)
GROUP BY MACHINE.ID
# Ordered by Subnet smart labels
ORDER BY LABEL.NAME - chucksteel 11 years ago
-
Wow, great. I get what you did I think. You had to LEFT JOIN it so it relates to a field in the other table so it could pull from the other columns? - joe.pyrczak 11 years ago
-
Yes, the join allows you to pull data from other tables based on a relationship between a table you already know and the other table. - chucksteel 11 years ago
