database view

SELECT dbo.Map_UnitType.typename AS CompanyTypeName,
      UnitType_1.typename AS CompanyParentTypeName, dbo.Map_UnitOwner.OwnerID,
      dbo.Map_UnitOwner.UnitID, dbo.Map_UnitOwner.OwnerName,
      dbo.Map_UnitOwner.OwnerType, dbo.Map_UnitOwner.address AS UnitOwnerAddress,
      dbo.Map_UnitOwner.introduction AS UnitOwnerIntroduction,
      dbo.yellow_CompanyInfo.CompanyName,
      dbo.yellow_CompanyInfo.introduction AS CompanyIntroduction,
      dbo.yellow_CompanyInfo.trafficinfo AS CompanyTrafficInfo,
      dbo.yellow_CompanyInfo.keyword AS CompaynKeyWord,
      dbo.yellow_CompanyInfo.CompanyID,
      dbo.yellow_CompanyInfo.address AS CompanyAddress,
      dbo.yellow_CompanyInfo.telephone AS CompanyTelephone,
      dbo.yellow_CompanyInfo.isActive AS CompanyIsActive,
      dbo.yellow_CompanyInfo.firstlevelcode AS CompanyType,
      dbo.Map_UnitOwner.isActive AS UnitOwnerIsActive,
      dbo.yellow_CompanyInfo.CompanyTypeID1, dbo.Map_CityUnit.parentUnitID,
      dbo.Map_UnitType.typeid AS CompanyTypeID,
      UnitType_1.typeid AS CompanyParentTypeID,
      dbo.yellow_CompanyInfo.keywords AS CompanyKeyWords,
      dbo.Map_UnitOwner.keywords AS UnitOwnerKeyWords,
      dbo.yellow_CompanyInfo.ECode
FROM dbo.Map_UnitOwner LEFT OUTER JOIN
      dbo.Map_CityUnit ON
      dbo.Map_UnitOwner.UnitID = dbo.Map_CityUnit.UnitID FULL OUTER JOIN
      dbo.yellow_CompanyInfo LEFT OUTER JOIN
      dbo.Map_UnitType UnitType_1 ON
      RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 4), 3)
      = UnitType_1.typeid LEFT OUTER JOIN
      dbo.Map_UnitType ON RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 8), 3)
      = dbo.Map_UnitType.typeid ON
      dbo.Map_UnitOwner.OwnerID = dbo.yellow_CompanyInfo.OwnerID
WHERE (dbo.Map_UnitOwner.OwnerID > 0) AND
      (dbo.yellow_CompanyInfo.modifytype != 3 OR
      dbo.yellow_CompanyInfo.modifytype = NULL) AND
      (dbo.Map_UnitOwner.modifytype != 3 OR
      dbo.Map_UnitOwner.modifytype = NULL))
UNION
(SELECT NULL AS CompanyTypeName,
      UnitType_1.typename AS CompanyParentTypeName, dbo.Map_UnitOwner.OwnerID,
      dbo.Map_UnitOwner.UnitID, dbo.Map_UnitOwner.OwnerName,
      dbo.Map_UnitOwner.OwnerType, dbo.Map_UnitOwner.address AS UnitOwnerAddress,
      dbo.Map_UnitOwner.introduction AS UnitOwnerIntroduction, NULL
      AS CompanyName, NULL AS CompanyIntroduction, NULL
      AS CompanyTrafficInfo, NULL AS CompaynKeyWord, NULL AS CompanyID, NULL
      AS CompanyAddress, NULL AS CompanyTelephone, NULL
      AS CompanyIsActive, NULL AS CompanyType,
      dbo.Map_UnitOwner.isActive AS UnitOwnerIsActive, NULL AS CompanyTypeID1,
      dbo.Map_CityUnit.parentUnitID, dbo.Map_UnitType.typeid AS CompanyTypeID,
      UnitType_1.typeid AS CompanyParentTypeID, NULL AS CompanyKeyWords,
      dbo.Map_UnitOwner.keywords AS UnitOwnerKeyWords, NULL AS ECode
FROM dbo.Map_UnitOwner LEFT OUTER JOIN
      dbo.Map_CityUnit ON
      dbo.Map_UnitOwner.UnitID = dbo.Map_CityUnit.UnitID FULL OUTER JOIN
      dbo.yellow_CompanyInfo LEFT OUTER JOIN
      dbo.Map_UnitType UnitType_1 ON
      RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 4), 3)
      = UnitType_1.typeid LEFT OUTER JOIN
      dbo.Map_UnitType ON RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 8), 3)
      = dbo.Map_UnitType.typeid ON
      dbo.Map_UnitOwner.OwnerID = dbo.yellow_CompanyInfo.OwnerID
WHERE (dbo.Map_UnitOwner.OwnerID > 0) AND (dbo.Map_UnitOwner.modifytype != 3 OR
      dbo.Map_UnitOwner.modifytype = NULL) AND
      (dbo.Map_UnitOwner.OwnerID NOT IN
          (SELECT dbo.Map_UnitOwner.OwnerID
         FROM dbo.Map_UnitOwner LEFT OUTER JOIN
               dbo.Map_CityUnit ON
               dbo.Map_UnitOwner.UnitID = dbo.Map_CityUnit.UnitID FULL OUTER JOIN
               dbo.yellow_CompanyInfo LEFT OUTER JOIN
               dbo.Map_UnitType UnitType_1 ON
               RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 4), 3)
               = UnitType_1.typeid LEFT OUTER JOIN
               dbo.Map_UnitType ON
               RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 8), 3)
               = dbo.Map_UnitType.typeid ON
               dbo.Map_UnitOwner.OwnerID = dbo.yellow_CompanyInfo.OwnerID
         WHERE (dbo.Map_UnitOwner.OwnerID > 0) AND
               (dbo.yellow_CompanyInfo.modifytype != 3 OR
               dbo.yellow_CompanyInfo.modifytype = NULL) AND
               (dbo.Map_UnitOwner.modifytype != 3 OR
               dbo.Map_UnitOwner.modifytype = NULL))))