CODEWITHSUNDEEP
sql
Samir Shah
Samir Shah

Reputation: 11

How to write SQL query to ignore duplicate row with null value

My View shows Duplicate row which i don't want.

I am geting

1, YM
1, NULL
2, YM
2, NULL

With below Code

SELECT 
   dbo.Store.SID,
   CASE WHEN dbo.Store.SID <> dbo.FileStore.SID THEN NULL 
        WHEN dbo.FileStore.MailSent = 'M' THEN 'YM' 
        WHEN dbo.FileStore.SID = dbo.Store.SID AND dbo.FileStore.FileType = 1 THEN 'Y' 
   ELSE NULL END AS FM 
FROM 
dbo.STORE 
    INNER JOIN dbo.FileStore ON dbo.Store.SID = dbo.FileStore.SID

I am looking for 

1 YM
2 YM

Upvotes: 0

Views: 78

Answers (2)

srworksalot
srworksalot

Reputation: 91

SELECT 
dbo.Store.SID
,CASE 
   WHEN dbo.Store.SID <> dbo.FileStore.SID THEN NULL --will never happen since it's an inner join
   WHEN dbo.FileStore.MailSent = 'M' THEN 'YM' 
   WHEN dbo.FileStore.SID = dbo.Store.SID --will happen always since it's an inner join
     AND dbo.FileStore.FileType = 1 THEN 'Y' 
   ELSE NULL -- this is the cause for Null, you have FileStore.MailSent <> 'M'
 END AS FM
FROM dbo.STORE 
INNER JOIN dbo.FileStore
     ON dbo.Store.SID = dbo.FileStore.SID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You appear to want filtering. If I understand correctly:

SELECT s.SID,
       (CASE WHEN fs.MailSent = 'M' THEN 'YM'
             WHEN fs.FileType = 1 THEN 'Y' 
        END) AS FM
FROM dbo.STORE s INNER JOIN
     dbo.FileStore fs
     ON s.SID = fs.SID
WHERE fs.MailSent = 'M' OR fs.FileType = 1;

There is no reason to repeat the JOIN conditions in the CASE expression. You know they are true because of the JOIN.

Upvotes: 1

Related Questions