Report Not Returning All Values
I have this query that works and returns all info that I need. The "*" are intentional. In the actual query there are software titles.
SELECT MACHINE.NAME AS SYSTEM_NAME, IP, LAST_INVENTORY, SOFTWARE.DISPLAY_NAME, BIOS_SERIAL_NUMBER AS SERIAL, USER_LOGGED USER, REPLACE((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=5443),'<br/>','|-|') AS PATHS FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID WHERE (1 in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 5443 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE LIKE '%%')) AND ( SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' ) ORDER BY SOFTWARE.DISPLAY_NAME asc
I added some lines so that our inventory tag number and department would be included. It's the same report, but just joining the asset table. This only returns 614 results while the one above returns over 900. Since I'm fairly new to SQL I was hoping someone could tell me what I was doing wrong. I'm wondering if since some of the fields in the asset table are NULL they are not showing.
SELECT MACHINE.NAME AS SYSTEM_NAME, IP, LAST_INVENTORY, SOFTWARE.DISPLAY_NAME, BIOS_SERIAL_NUMBER AS SERIAL, USER_LOGGED USER, ASSET_DATA_5.FIELD_27 as Location,ASSET_DATA_5.FIELD_38 as Department, ASSET_DATA_5.FIELD_25 as Tag_Number, REPLACE((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=5443),'<br/>','|-|') AS PATHS FROM MACHINE JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID WHERE (1 in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 5443)) AND ( SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' OR SOFTWARE.DISPLAY_NAME like '%*%' ) ORDER BY SOFTWARE.DISPLAY_NAME asc
Answers (1)
Use LEFT JOIN instead of JOIN.
The JOIN statements will exclude null values. Try substituting a LEFT JOIN statement for one/some/all of the JOINs and see if that helps. LEFT JOINs will include rows (results) that have null values in the data targeted in the join (which is what allow you to include other tables in your report). For more info, check this out:
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
John
Comments:
