I have tried the CTE and subquery approaches in my query below but it didn't work.
I have two tables with two columns; I would like to use only one column for the join condition,
union all, and
group by as per the requirement. I would like to fetch the other column without mentioning it among the
group by columns. How can achieve this?
SELECT BusnPartClientSuppContractId ,DivBranchId FROM ( SELECT A.BusnPartClientSuppContractId ,A.DivBranchId FROM tblBusnPartClientSuppContractDivBranch AS A WHERE A.BusnPartClientSuppContractId IN ( SELECT BusnPartClientSuppContractId FROM @BusnpartIdsOfOtherContracts ) AND A.BusnPartClientSuppContractId NOT IN ( SELECT TOP 1 BusnPartClientSuppContractId FROM @SuppClientDivBranch2 ) UNION ALL SELECT B.BusnPartClientSuppContractId ,B.DivBranchId FROM @SuppClientDivBranch2 AS B ) tmp GROUP BY BusnPartClientSuppContractId ,DivBranchId HAVING COUNT(*) = 1 ORDER BY DivBranchId
BusnPartClientSuppContractId but I dont want to include it in the
group by clause.