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
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
