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
Answer Summary:
Use LEFT JOIN instead of JOIN.
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

4

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

Answered 07/16/2012 by: jverbosk
Red Belt

  • Thanks John. That was one of those end of day reports that was asked, and I figured it was something small I was overlooking.
  • No problem. I know the feeling - some of the best work I've ever done has been at the very end of the day... >_<

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