drop view if exists vw_company_credential;
CREATE VIEW `vw_company_credential` AS SELECT
`t1`.`DOMAINID` AS `domainId`,
`t2`.`name` AS `comName`,
`t3`.`ITEM_legalPerson` AS `legalPerson`,
`t3`.`ITEM_companyPhone` AS `tel`,
`t3`.`ITEM_registeredCapital` AS `registeredMoney`,
`t3`.`ITEM_businessScope` AS `securityScopeType`,
`t3`.`ITEM_securityServicePermitNo` AS `cerNo`,
`t3`.`ITEM_issueServiceDate` AS `cerDate`,
concat(
       ifnull( `t2`.`place_of_register_province_name`, '' ),
       ifnull( `t2`.`place_of_register_city_name`, '' ),
       ifnull( `t2`.`place_of_register_district_name`, '' ),
       ifnull( `t2`.`place_of_register_address`, '' )) AS `address`,
concat(
       ifnull( `t2`.`place_of_business_province_name`, '' ),
       ifnull( `t2`.`place_of_business_city_name`, '' ),
       ifnull( `t2`.`place_of_business_district_name`, '' ),
       ifnull( `t2`.`place_of_business_address`, '' )) AS `location`
FROM `baibaodunflow`.`tlk_setsupervise` `t1`
INNER JOIN `tenant_organizations` `t2` on `t1`.`DOMAINID` = `t2`.`id` AND `t2`.`institutional_code` = '保安服务公司'
LEFT JOIN `baibaodunflow`.`tlk_companyinformation` `t3` on `t1`.`DOMAINID` = `t3`.`DOMAINID`
WHERE `t1`.`ITEM_REGISTERADDRESSID` LIKE '11%'
;