You must Sign In to post a response.
  • Category: SQL Server

    How to combine two query

    My query as follows


    select Av.Associate_ID as Associateid,Av.IsActive,CAD.value,CAD.ValidateFlag,CGA.facility
    from
    [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] Av
    inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
    ON CAD.[Associate_ID]=Av.[Associate_ID]
    inner join [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA
    ON CGA.[Associate_ID]=Av.[Associate_ID]
    where CGA.[Associate_ID] = '101012'(select
    "Address" = LTRIM(RTRIM((LTRIM(RTRIM(CAA.Address1)) + ', '+
    CASE WHEN LTRIM(RTRIM(CAA.Address2))!='' THEN LTRIM(RTRIM(CAA.Address2)) + ', ' ELSE '' END +
    CASE WHEN LTRIM(RTRIM(CAA.Address3))!='' THEN LTRIM(RTRIM(CAA.Address3)) + ', ' ELSE '' END+
    LTRIM(RTRIM((COALESCE(replace(CAA.address4,' ',''),'')+ CASE WHEN COALESCE(caa.Address4,'') !='' THEN + ', '+
    CASE WHEN COALESCE(RTRIM(CAA.city),'')!='' THEN COALESCE(RTRIM(CAA.city),'') END + ' '+ ', '+
    CASE WHEN COALESCE(RTRIM(CAA.Pincode),'')!='' THEN COALESCE(RTRIM(CAA.Pincode),'') ELSE '' END
    ELSE COALESCE(RTRIM(CAA.city),'')+', '+COALESCE(RTRIM(CAA.Pincode),'') END )))))) FROM
    [OneC_988].[dbo].[988_Details_AssociateProfile] AP
    LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] CAD
    ON CAD.[Associate_ID]=AP.[varchar_AssociateID]
    LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CONT
    ON CONT.Associate_ID=AP.[varchar_AssociateID] AND CONT.Contact_Type ='MBL1'-- AND CONT.ValidateFlag = 'S'
    LEFT OUTER JOIN [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA
    ON CGA.[Associate_ID]=AP.[varchar_AssociateID]
    LEFT JOIN CentralRepository..vw_CentralRepository_AssociateAddress CAA
    ON CAA.Associate_ID=AP.[varchar_AssociateID]
    AND AddressType IN (SELECT CASE WHEN COALESCE(COUNT(Associate_ID),0) >= 1 THEN 'Mail' ELSE 'Home' END
    FROM CentralRepository..vw_CentralRepository_AssociateAddress))

    When i run the above query shows output as follows

    Assoicate IsActive Value Validateflag Facility
    101012 A 984/123-4567 S CHN - NVL

    Address
    Street


    Using above query i want output as follows

    Assoicate IsActive Value Validateflag Facility Address
    101012 A 984/123-4567 S CHN - NVL Street


    for getting a above output how can i do.
  • #766303
    Hi

    if you combine Query means using inner join or using union Query for this



    select * from Tb_Country
    union
    select * from Tb_City
    union
    select * from Tb_State


    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766308
    Hi,

    In your case Union/ Union All etc... are not working because for applying Union you need same no of columns & same name of the fields in both the tables, but in your case in result1 you have 4 columns but in result2 you have only 1 column, so union is not applicable in that case.

    My suggestion is use Join condition to achieve your requirement, As per Michrosoft terminology we have different type of joins available in SQL Server, based on your requirement you can go with appropriate join.

    Refer below link to understand more about Joins in SQL Server.

    http://www.dotnet-tricks.com/Tutorial/sqlserver/W1aI140312-Different-Types-of-SQL-Joins.html

    Hope this will helpful to you....

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766321
    Hi,

    Use this below query, to get the expected output

    (select Av.Associate_ID as Associateid,Av.IsActive,CAD.value,CAD.ValidateFlag,CGA.facility,addr.Address
    from
    [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] Av
    inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
    ON CAD.[Associate_ID]=Av.[Associate_ID]
    inner join [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA
    ON CGA.[Associate_ID]=Av.[Associate_ID]
    outer apply
    (select
    "Address" = LTRIM(RTRIM((LTRIM(RTRIM(CAA.Address1)) + ', '+
    CASE WHEN LTRIM(RTRIM(CAA.Address2))!='' THEN LTRIM(RTRIM(CAA.Address2)) + ', ' ELSE '' END +
    CASE WHEN LTRIM(RTRIM(CAA.Address3))!='' THEN LTRIM(RTRIM(CAA.Address3)) + ', ' ELSE '' END+
    LTRIM(RTRIM((COALESCE(replace(CAA.address4,' ',''),'')+ CASE WHEN COALESCE(caa.Address4,'') !='' THEN + ', '+
    CASE WHEN COALESCE(RTRIM(CAA.city),'')!='' THEN COALESCE(RTRIM(CAA.city),'') END + ' '+ ', '+
    CASE WHEN COALESCE(RTRIM(CAA.Pincode),'')!='' THEN COALESCE(RTRIM(CAA.Pincode),'') ELSE '' END
    ELSE COALESCE(RTRIM(CAA.city),'')+', '+COALESCE(RTRIM(CAA.Pincode),'') END )))))) FROM
    [OneC_988].[dbo].[988_Details_AssociateProfile] AP
    LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] CAD
    ON CAD.[Associate_ID]=AP.[varchar_AssociateID]
    LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CONT
    ON CONT.Associate_ID=AP.[varchar_AssociateID] AND CONT.Contact_Type ='MBL1'-- AND CONT.ValidateFlag = 'S'
    LEFT OUTER JOIN [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA
    ON CGA.[Associate_ID]=AP.[varchar_AssociateID]
    LEFT JOIN CentralRepository..vw_CentralRepository_AssociateAddress CAA
    ON CAA.Associate_ID=AP.[varchar_AssociateID]
    AND AddressType IN (SELECT CASE WHEN COALESCE(COUNT(Associate_ID),0) >= 1 THEN 'Mail' ELSE 'Home' END
    FROM CentralRepository..vw_CentralRepository_AssociateAddress)) addr
    where CGA.[Associate_ID] = '101012')


  • Sign In to post your comments