SELECT ad.ORG_NAME AS "Account",
Resources.Resourcename "Asset name",
productType.COMPONENTTYPENAME AS "Product Type",
product.COMPONENTNAME AS "Product", state.displaystate AS "Asset State" FROM Resources
LEFT JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceType ON productType.RESOURCETYPEID=ResourceType.RESOURCETYPEID
LEFT JOIN ResourceCategory rCategory ON productType.RESOURCECATEGORYID=rCategory.RESOURCECATEGORYID
LEFT JOIN ResourceState state ON resources.RESOURCESTATEID=state.RESOURCESTATEID
INNER JOIN AccountSiteMapping asm ON resources.SITEID=asm.SITEID
INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
WHERE (((rCategory.CATEGORY like 'IT') AND (ResourceType.TYPE like 'Asset')) AND (state.RESOURCESTATEID NOT IN (4,5))) ORDER BY 1
DB: MSSQL & PGSQL