SQL Query Group By inclusion

Question

Can someone help modify the following SQL script so that I'm able to do a GROUP BY and replace the character (null).

At the moment I have two tables:

Table 1

PK_LOYALTYACCOUNT     COUNTRY_ID     CDC_TYPE
int                   varchar(50)    varchar(50)
666                   DE             U
98                    DE             U
27975373              DE             U
666                   DE             N

Table 2

PK_LOYALTYACCOUNT     COUNTRY_ID     CDC_TYPE
int                   varchar(50)    varchar(50)
666                   DE             U
75                    DE             U
27975370              DE             U
578                   WE             N
54                    RT             N

The results from the sql script produces the following table:

is_deleted     PK_LOYALTYACCOUNT     COUNTRY_ID     CDC_TYPE
int            int                   varchar(50)    varchar(50)
1              666                   DE             U
0              666                   DE             U
(null)         769                   DE             U
(null)         578                   WE             N
(null)         54                    RT             N

However, I would like to group by the table (in any fashion) and remove or replace the null values, such that the tables appear something as follows:

COUNTRY_ID     CDC_TYPE     PK_LOYALTYACCOUNT    is_deleted

DE             U            666                  1
DE             U            666                  0
RT             N            54                   replace null
WE             N            578                  replace null
DE             N            769                  replace null

The sql script is as follows:

with cmn as 
  ( SELECT a.CDC_TYPE,
           a. PK_LOYALTYACCOUNT, --Add these also in CTE result set 
           a.COUNTRY_ID --Add these also in CTE result set 
    FROM  tabled  a  
    INNER JOIN tablee b  
    ON a.COUNTRY_ID = b.COUNTRY_ID 
    AND a.PK_LOYALTYACCOUNT = b.PK_LOYALTYACCOUNT  
    AND a.CDC_TYPE = 'U'
    )
 SELECT 1 AS is_deleted, 
        a.*  
 FROM  tabled  a  
 INNER JOIN cmn  
 ON a.CDC_TYPE = cmn.CDC_TYPE 
 and  a.COUNTRY_ID = cmn.COUNTRY_ID  
 AND a.PK_LOYALTYACCOUNT = cmn.PK_LOYALTYACCOUNT

 UNION ALL  
 SELECT 0 AS is_deleted, 
        b.*  
 FROM tablee  b  
 INNER JOIN cmn  
 ON b.CDC_TYPE = cmn.CDC_TYPE 
 and b.COUNTRY_ID = cmn.COUNTRY_ID 
 AND b.PK_LOYALTYACCOUNT = cmn.PK_LOYALTYACCOUNT
UNION ALL 
SELECT NULL AS CDC_TYPE,
       a.* 
FROM   tabled a 
WHERE  a.CDC_TYPE = 'N' 
UNION ALL 
SELECT NULL AS CDC_TYPE, 
       b.* 
FROM   tablee b 
WHERE  b.CDC_TYPE = 'N'

Thanks in advance

Answer 1

You could perhaps use a string for the "is_deleted" in all of those unioned queries.

But then maybe this query could also be simplified via a FULL JOIN

For example:

SELECT PK_LOYALTYACCOUNT, COUNTRY_ID, CDC_TYPE, IS_DELETED
FROM
(
    SELECT 
    COALESCE(d.PK_LOYALTYACCOUNT, e.PK_LOYALTYACCOUNT) AS PK_LOYALTYACCOUNT, 
    COALESCE(d.COUNTRY_ID, e.COUNTRY_ID) AS COUNTRY_ID, 
    COALESCE(d.CDC_TYPE, e.CDC_TYPE) AS CDC_TYPE,
    (CASE 
     WHEN d.CDC_TYPE = e.CDC_TYPE AND d.CDC_TYPE = 'U' THEN '1' 
     WHEN d.CDC_TYPE = e.CDC_TYPE AND d.CDC_TYPE = 'N' THEN '0' 
     ELSE '' 
     END) AS IS_DELETED
    FROM tabled d
    FULL JOIN tablee e 
      ON e.PK_LOYALTYACCOUNT = d.PK_LOYALTYACCOUNT 
     AND e.COUNTRY_ID = d.COUNTRY_ID 
     AND e.CDC_TYPE = d.CDC_TYPE
    WHERE d.CDC_TYPE = e.CDC_TYPE 
       OR d.CDC_TYPE = 'N'
       OR e.CDC_TYPE = 'N'
) AS cmn 
GROUP BY PK_LOYALTYACCOUNT, COUNTRY_ID, CDC_TYPE, IS_DELETED
ORDER BY IS_DELETED DESC, PK_LOYALTYACCOUNT, COUNTRY_ID, CDC_TYPE;