Hi,

I've created a sql report for a number of different assets. The report runs fine, but I find that each asset has it's own columns. The columns are all the same just different tables for different items. Is there a way to display only 1 set of columns ie serial #, model #, mac instead of serial#, model#,mac, serial#, model#,mac.

Here's my sql code

SELECT ASSET.ID AS ASSET_ID,ASSET_DATA_13.FIELD_56 AS FIELD_56,ASSET.NAME AS ASSET_NAME,RD60.FIELD_59 AS R60_FIELD_59,RD60.FIELD_57 AS R60_FIELD_57,RD60.FIELD_58 AS R60_FIELD_58,RD269.FIELD_96 AS R269_FIELD_96,RD269.FIELD_94 AS R269_FIELD_94,RD269.FIELD_95 AS R269_FIELD_95,RD126.FIELD_87 AS R126_FIELD_87,RD126.FIELD_85 AS R126_FIELD_85,RD126.FIELD_86 AS R126_FIELD_86 FROM ASSET_DATA_13 LEFT JOIN ASSET ON ASSET_DATA_13.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=13 LEFT JOIN ASSET_ASSOCIATION JR60 ON JR60.ASSOCIATED_ASSET_ID = ASSET.ID AND JR60.ASSET_FIELD_ID=60
LEFT JOIN ASSET R60 ON R60.ID = JR60.ASSET_ID
LEFT JOIN ASSET_DATA_17 RD60 ON RD60.ID=R60.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION JR269 ON JR269.ASSOCIATED_ASSET_ID = ASSET.ID AND JR269.ASSET_FIELD_ID=269
LEFT JOIN ASSET R269 ON R269.ID = JR269.ASSET_ID
LEFT JOIN ASSET_DATA_21 RD269 ON RD269.ID=R269.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION JR126 ON JR126.ASSOCIATED_ASSET_ID = ASSET.ID AND JR126.ASSET_FIELD_ID=126
LEFT JOIN ASSET R126 ON R126.ID = JR126.ASSET_ID
LEFT JOIN ASSET_DATA_20 RD126 ON RD126.ID=R126.ASSET_DATA_ID WHERE (ASSET.NAME = 'building X') ORDER BY ASSET_ID
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
Look into the MYSQL union command.

Basically it allows you to "stack" multiple select statements on top of each other. I would help with the query but each Kbox has a different asset layout, so there's no way to test.
Answered 03/27/2012 by: dchristian
Red Belt

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