user1817661
user1817661

Reputation: 83

SQL Server - Get record where not exist on other table

I have two tables:

enter image description here

enter image description here

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

Because they don't exist in Jobs table.

Upvotes: 1

Views: 61

Answers (4)

Jonathan Willcock
Jonathan Willcock

Reputation: 5235

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;

Upvotes: 0

Konstantin
Konstantin

Reputation: 41

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

Upvotes: 0

Yusuf
Yusuf

Reputation: 212

you can use like this:

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

You can use a not exists condition:

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

Upvotes: 2

Related Questions