SELECT
     mt1.Ledger_ID AS old_id,
     mt1.GrpCompany_ID AS company_ID,
     mt1.LedgerName AS name,
     mt1.UserName AS userName,
     mt1.Password AS password,
     mt1.DateOfBirth AS dateOfBirth,
     mt1.AccountName AS  accountName,
     mt1.AddressType AS addressType,
     mt1.ZipCode AS zipCode,
     mt1.BusinessDoneDate AS businessDoneDate,
     mt1.ISVerify AS isVerified,
     mt1.LedgerType AS companyType,
     mt1.LedgerName AS contactName,
     mt1.Email AS officeEmail,
     mt1.HomeEmail AS homeEmail,
     mt1.Phone1 AS phoneNumber1,
     mt1.Phone2 AS phoneNumber2,
     mt1.Mobile1 AS mobileNumber1,
     mt1.Mobile2 AS mobileNumber2,
     mt1.Designation AS designation,
     mt1.ISHKSales as  isActive,
     mt1.DateOfAnnivesary as dateOfAnnivesary,
     mt1.StockCategory as stockCategory,
     mt1.DOB_Year as yearOfBirth,
     mt1.DOB_Month as monthOfBirth,
     mt1.DOB_Date as dayOfBirth,
     mt1.PartCode as partyCode,
     mt1.EntryDate,
     c.CountryName as country,
     city.CityName as city,
     s.StateName as state,
     mt1.Street as street,
     mt1.UpdateDate,
     mt1.EntryBy,
     mt1.UpdateBy,
     dept.DepartmentName as departmentName,
     dept.DepartmentType as departmentType,
     mt1.HOF_CountryName as hofCountry,
     mt1.ContTrfDate as transferDate,
     mt1.WeChat as weChat,
     mt1.QQ,
     mt1.Website as website,
     mt1.FAX as FAX,
     mt1.DefaltNarration as narration,
     mt1.isdefault
FROM MST_Ledger mt1
INNER JOIN ( SELECT
        mt2.Ledger_ID 
        FROM MST_Ledger mt2
    WHERE  
        (
            mt2.LedgerType LIKE '%BUYER%' OR
            mt2.LedgerType LIKE '%AADAT%' OR
            mt2.LedgerType LIKE '%BROKER%' OR
            mt2.LedgerType LIKE '%GROUPCOMPANY%' OR
            mt2.LedgerType LIKE '%AGENT%' OR
            mt2.LedgerType LIKE '%EXPORTPARTY%' OR
            mt2.LedgerType LIKE '%LOCAL PURCHASE%' OR
            mt2.LedgerType LIKE '%IMPORT%' OR
            mt2.LedgerType LIKE '%VENDOR%'
        )
        AND mt2.CRMType = 'COMPANY'
        AND mt2.Ledger_ID IS NOT NULL
        AND mt2.LedgerName IS NOT NULL
) mt3 on mt3.Ledger_ID = mt1.GrpCompany_ID
LEFT JOIN MST_Country c ON c.Country_ID = mt1.Country_ID
LEFT JOIN MST_City city ON city.City_ID=mt1.City_ID
LEFT JOIN MST_State s ON s.State_ID=mt1.State_ID
LEFT JOIN MST_Department dept ON dept.Department_ID=mt1.Department_ID
WHERE ( mt1.LedgerType LIKE '%BUYER%'
    OR mt1.LedgerType LIKE '%AADAT%'
    OR mt1.LedgerType LIKE '%BROKER%'
    OR mt1.LedgerType LIKE '%GROUPCOMPANY%'
    OR mt1.LedgerType LIKE '%AGENT%'
    OR mt1.LedgerType LIKE '%EXPORTPARTY%'
    OR mt1.LedgerType LIKE '%LOCAL PURCHASE%'
    OR mt1.LedgerType LIKE '%IMPORT%'
    OR mt1.LedgerType LIKE '%VENDOR%'
)
AND mt1.CRMType='CONTACT' AND mt1.Ledger_ID IS NOT NULL