1
select `cmp`.`ClientID` AS `ClientID`,`cmp`.`LocationID` AS `LocationID`,`cmp`.`ComputerID` AS `ComputerID`,`cmp`.`ContactID` AS `ContactID`,`sw`.`Name` AS `SoftwareName`,`sw`.`Version` AS `SoftwareVersion`,`sw`.`Location` AS `SoftwareLocation`,`sw`.`Size` AS `SoftwareSizeKB`,if((`sw`.`Size` > 1073741824),concat(round((((`sw`.`Size` / 1024) / 1024) / 1024),2),' TB'),if((`sw`.`Size` > 10485760),concat(round(((`sw`.`Size` / 1024) / 1024),0),' GB'),if((`sw`.`Size` > 1048576),concat(round(((`sw`.`Size` / 1024) / 1024),2),' GB'),if((`sw`.`Size` > 1024),concat(round((`sw`.`Size` / 1024),0),' MB'),concat(ifnull(`sw`.`Size`,0),' KB'))))) AS `SoftwareSizeText`,cast(`sw`.`DateInstalled` as date) AS `SoftwareDateInstalled`,if((`black`.`ID` > 0),1,0) AS `SoftwareIsBlacklist`,if((`white`.`ID` > 0),1,0) AS `SoftwareIsWhitelist` from (((`labtech`.`computers` `cmp` join `labtech`.`software` `sw` on((`cmp`.`ComputerID` = `sw`.`ComputerID`))) left join `labtech`.`applicationblacklist` `black` on((`sw`.`Name` = `black`.`Name`))) left join `labtech`.`applicationwhitelist` `white` on((`sw`.`Name` = `white`.`Name`))) group by `sw`.`ComputerID`,`sw`.`AppID`