Miscellaneous

Have created several logical assets and even managed to connect them as needed via available fields in the Asset Type pages. Then I created a Report that, in HTML or XLS formatting, displays the resulting connected assets beautifully. Because I want to use this data in a ticket queue I managed to gather the query being used to generate the Report.

My problem, challenge, major headache has been connecting the query components in the same manner a the report. Seems the report tool is creating a temporary table to store 'stage one' of the query then joining it to 'stage two'. As general users, we do not have the access to 'write' to report temp tables in Workbench.

I am looking for suggestions / help on connecting these sections or restructuring the query to produce the same results as the report. I've spent far too many hours on this already and need to complete this project yesterday. Thanks in advance for any suggestions.

Additional information I can provide -

ASSET_DATA_14 is the top level asset.
ASSET_DATA_16 is a Sub-Asset of 14.
ASSET_DATA_16 also has multiple Single Select fields connected to ASSET_DATA_15 and ASSET_DATA_19.

Here is the query used for the report.

SELECT ASSET.NAME AS ASSET_NAME,
ASSET_DATA_14.FIELD_10052 AS FIELD_10052,
ASSET_DATA_14.FIELD_10036 AS FIELD_10036,
ASSET_DATA_14.FIELD_10051 AS FIELD_10051,
ASSET.ID as TOPIC_ID
FROM ASSET_DATA_14
LEFT JOIN ASSET ON ASSET_DATA_14.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=14
LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID
ORDER BY ASSET_NAME
SELECT ASSET.NAME AS ASSET_NAME,
ASSET_DATA_16.FIELD_10043 AS FIELD_10043,
ASSET_DATA_16.FIELD_10053 AS FIELD_10053,
A10055.NAME AS FIELD_10055,
A10062.NAME AS FIELD_10062,
A10063.NAME AS FIELD_10063,
A10065.NAME AS FIELD_10065,
ASSET.ID as TOPIC_ID,
GROUP_CONCAT(DISTINCT REPORT_TEMP.TT1.ROW ORDER BY REPORT_TEMP.TT1.ROW) AS PARENT_ROW
FROM ASSET_DATA_16
LEFT JOIN ASSET ON ASSET_DATA_16.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=16
LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID
LEFT JOIN ASSET_ASSOCIATION J10055 ON J10055.ASSET_ID = ASSET.ID AND J10055.ASSET_FIELD_ID=10055
LEFT JOIN ASSET A10055 ON A10055.ID = J10055.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_15 AD10055 ON AD10055.ID = A10055.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J10062 ON J10062.ASSET_ID = ASSET.ID AND J10062.ASSET_FIELD_ID=10062
LEFT JOIN ASSET A10062 ON A10062.ID = J10062.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_19 AD10062 ON AD10062.ID = A10062.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J10063 ON J10063.ASSET_ID = ASSET.ID AND J10063.ASSET_FIELD_ID=10063
LEFT JOIN ASSET A10063 ON A10063.ID = J10063.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_19 AD10063 ON AD10063.ID = A10063.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J10065 ON J10065.ASSET_ID = ASSET.ID AND J10065.ASSET_FIELD_ID=10065
LEFT JOIN ASSET A10065 ON A10065.ID = J10065.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_19 AD10065 ON AD10065.ID = A10065.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION PARENT_AA14 ON PARENT_AA14.ASSET_ID = ASSET.ID
LEFT JOIN ASSET PARENT_A14 ON PARENT_A14.ID = PARENT_AA14.ASSOCIATED_ASSET_ID AND PARENT_A14.ASSET_TYPE_ID = 14
JOIN REPORT_TEMP.TT1 ON (REPORT_TEMP.TT1.ID = PARENT_A14.ID)
GROUP BY ASSET_DATA_16.ID ORDER BY ASSET_NAME