CODEWITHSUNDEEP
sqlsql-server
Doron Abramovich
Doron Abramovich

Reputation: 19

Am i using "EXCEPT" wrong? (SQL)

Why im getting only one resault out of using Except here?

Code:

Select C1.*
From tblCampaign as C1 
inner join tblCampaignInSocialMedia as CISM 
    On C1.campaignCode = CISM.campaignCode  
         --Except 
Select C2.*
From tblCampaign as C2 
inner join tblSentToMember as STM 
    On C2.campaignCode = STM.campaignCode

I'm trying to get those two rows of index's 6,7 :

1

for some reason I get only one of the rows :

2

the result I'm trying to get :

3

First post here, be gentle :)

Upvotes: 0

Views: 254

Answers (2)

eshirvana
eshirvana

Reputation: 24568

because Except returns distinct rows by comparing the results of two queries.

here is microsoft refrence doc

probably you want to do this :

Select C1.*
From tblCampaign as C1 
inner join tblCampaignInSocialMedia as CISM 
    On C1.campaignCode = CISM.campaignCode  
    and c1.campaignCode not in (
                            SELECT c2.campaignCode
                            From tblCampaign as C2 
                            inner join tblSentToMember as STM 
                                On C2.campaignCode = STM.campaignCode
                            )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The two rows that you want are identical.

EXCEPT removes duplicates, similar to UNION. Unfortunately, SQL Server doesn't support an EXCEPT ALL operator. You would need to rephrase the query, perhaps using NOT EXISTS instead of EXCEPT.

That would probably look something like this:

select c.*
from tblCampaign c
where exists (select 1 
              from tblCampaignInSocialMedia cism 
              where c.campaignCode = cism.campaignCode  
             ) and
      not exists (select 1
                  from tblSentToMember stm
                  where c.campaignCode = stm.campaignCode
                 );

Upvotes: 1

Related Questions