SQLTeam.com | Weblogs | Forums

Not exists query

How to get spare no from categories 1 table where not exist per same spare no on table categories 2?

How to get spare no from categories 1 table where not exist per same spare no on table categories 2?

so i need to make select statement query select spare no from table categories 1 that have different categories

on table categories 2 per same spare no

as example spare no 1350 have category 5902 on table categories1 but on table categories2

i have same same spare no 1350 but have different categories as 7090 and 4020

then i select or display this spare no from table categories 1

as example spare no 1200 have category 5050 on table categories1 but on table categories2

i have same same spare no 1200 but have same categories as 5050 on table categories 2

so i don't need it or don't need to display it because it exist same spare no and same category on table categories 2

so How to make select query give me expected result below ?

create table #categories1
(
catId int identity(1,1),
SpareNo int,
CategoryId int,
)
insert into #categories1(SpareNo,CategoryId)
values
(1200,5050),
(1350,5902),
(1700,8070),
(1990,2050),
(7000,2030)

create table #categories2
(
catId int identity(1,1),
SpareNo int,
CategoryId int,
)
insert into #categories(SpareNo,CategoryId)
values
(1200,5050),
(1200,5090),
(1200,5070),
(1350,7090),
(1350,4020),
(1700,8612),
(1990,7575),
(1990,2050),
(7000,4200),
(7000,4500)

expected result :
catId SpareNo CategoryId
2 1350 5902
3 1700 8070
5 7000 2030

image

what I try

select c.* from #categories1 c
left join #categories2 c2 on c.SpareNo=c2.SpareNo and c.categoryid=c2.categoryid
where c2.catid is null

but it not give me expected result as above

hope this helps

image