SQL Server - Get record where not exist on other table-CodePudding

I have two tables:

I want to get result in Campaign table - > 55,100.

Because they don't exist in Jobs table.

CodePudding user response：

You can use a not exists condition:

SELECT *
FROM   campaign c
WHERE  NOT EXISTS (SELECT *
                   FROM   jobs j
                   WHERE  j.campaignid = c.campaignid)

CodePudding user response：

you can use like this:

select * from campaign
 where 1 = 1
   and campaignid is not null
   and campaignid not in (select campaignid from jobs)

CodePudding user response：

create table #Campaign (CampaignId int NULL)
create table #Jobs (JobId int, CampaignId int)

insert into #Campaign values (1),(2),(3),(4),(100),(55)
insert into #Jobs values (1,1),(2,2),(3,3),(4,4),(5,1),(6,3),(7,3)

select distinct C.CampaignId
from #Campaign as C
where not exists(select 1 from #Jobs as J where C.CampaignId = J.CampaignId)

Result is 55 100

CodePudding user response：

Or you can use a LEFT OUTER JOIN where the id is null:

SELECT c.* from campaign c
LEFT OUTER JOIN jobs j ON j.CampaignId = c.CampaignId
WHERE j.CampaignId IS NULL;