CODEWITHSUNDEEP
sqlsql-server
Carltonp
Carltonp

Reputation: 1344

SQL Query Comparison with UNION Condition

We the help of colleagues I have compiled the following sql code to compare and strip out records that appear two tables.

WITH cmn AS 
(
    SELECT a.CDC_TYPE
    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 

UNION ALL 

SELECT 
    0 AS is_deleted, 
    b.* 
FROM
    tablee b 
INNER JOIN 
    cmn ON b.CDC_TYPE = cmn.CDC_TYPE

The the sample data is from the following tables:

tabled

tablee

The expected result from the query should simply look like:

correctout

However, the output looks like the following:

incorrectoutput

Can someone let me know where I'm going wrong?

Thanks

Upvotes: 0

Views: 83

Answers (1)

Deepshikha
Deepshikha

Reputation: 10264

You should write the query as: 

; 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    a  
    INNER JOIN  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    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    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

when you return only CDC_TYPE 'U' from CTE and joins the value again with table a and b it actually gets multiplied with all the rows of table a and b respectively as all of them qualifies the condition a.CDC_TYPE = cmn.CDC_TYPE. To avoid getting all results you should include PK_LOYALTYACCOUNT and COUNTRY_ID in the CTE result set. Test code here...

Upvotes: 1

Related Questions