/build/static/layout/Breadcrumb_cap_w.png

SQL report

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

Answers (1)

Posted by: dchristian 12 years ago
Red Belt
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.

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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ