Select  C.Name As CompanyName,
        R.Name As CompanyRegion,
        Ci.Name CmpanayCity,
        C1.Name As CompanyAssotiation,
        R1.Name As CompanyAssotiationRegion,
        Ci1.Name CmpanayAssotiationCity,
        N1.Shop_qnt As AssociationQnt,
        Cv.DictionaryValue As AssociationType,
        Cn.Name As OtvMenager
From [dbo].[Company] C
 Left Outer Join [dbo].[Association] A On C.id = A.CompanyTo_id
 Left Outer Join [dbo].[Company] C1 On C1.id = A.CompanyFrom_id
 Left Outer Join [dbo].[Region] R On C.Region_id = R.ID
 Left Outer Join [dbo].[City] Ci On C.City_id = Ci.ID
 Left Outer Join [dbo].[Region] R1 On C1.Region_id = R1.ID
 Left Outer Join [dbo].[City] Ci1 On C1.City_id = Ci1.ID
 Left Outer Join [dbo].[NetShops]  N1 on C1.ID = N1.Company_id
 Left Outer Join [dbo].[CustomDictionaryValue] Cv On Cv.id = N1.ShopType_id
 Left Outer Join [dbo].[Contact] Cn On C.AccountManager_id = Cn.ID
where
 C.ID In (Select pc.id From [dbo].[Company] pc , [dbo].[CompanyInfo] pci
         Where CompanyType_id In (
                              Select id
                              From [dbo].[CompanyType]
                              Where Code = 'BaseCompany')
                              And pc.id = pci.subj_id
                              And pci.DictValue_id In (
                                  Select id
                                  From [dbo].[CustomDictionaryValue]
                                  Where Code = 'ACTIVE'))
 And A.FromRole_id in (
                   Select id
                   From [dbo].[AssRoleType]
                   Where name = 'клиент')
Union
Select  C.Name As CompanyName,
        R.Name As CompanyRegion,
        Ci.Name CmpanayCity,
        C.Name As CompanyAssotiation,
        R.Name As CompanyAssotiationRegion,
        Ci.Name CmpanayAssotiationCity,
        N.Shop_qnt As AssociationQnt,
        Cv.DictionaryValue As AssociationType,
        Cn.Name As OtvMenager
From [dbo].[Company] C
 Left Outer Join [dbo].[Association] A On C.id = A.CompanyTo_id
 Left Outer Join [dbo].[Company] C1 On C1.id = A.CompanyFrom_id
 Left Outer Join [dbo].[Region] R On C.Region_id = R.ID
 Left Outer Join [dbo].[City] Ci On C.City_id = Ci.ID
 Left Outer Join [dbo].[NetShops]  N on C.ID = N.Company_id
 Left Outer Join [dbo].[CustomDictionaryValue] Cv On Cv.id = N.ShopType_id
 Left Outer Join [dbo].[Contact] Cn On C.AccountManager_id = Cn.ID
where C.ID in (Select pc.id From [dbo].[Company] pc , [dbo].[CompanyInfo] pci
                     Where CompanyType_id In (
                             Select id
                             From [dbo].[CompanyType]
                             Where Code = 'NetCompany')
                             And pc.id = pci.subj_id
                             And pci.DictValue_id in (
                               Select id
                               From [dbo].[CustomDictionaryValue]
                               Where DictionaryValue = 'прямые отгрузки'))
order by CompanyName, CompanyRegion, CmpanayCity, CompanyAssotiation,
         CompanyAssotiationRegion, CmpanayAssotiationCity, AssociationQnt,
         AssociationType, OtvMenager