How to get spare no from categories 1 table where not exist per same s

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
1. --===== 1. WHERE NOT EXISTS method
2. SELECT c1.*
3. FROM #categories1 c1
4. WHERE NOT EXISTS (SELECT *
5. FROM #categories2 c2
6. WHERE c1.SpareNo = c2.SpareNo
7. AND c1.CategoryID = c2.CategoryId)
8. ;
9. GO
10. --===== 2. LEFT OUTER JOIN method
11. SELECT c1.*
12. FROM #categories1 c1
13. LEFT JOIN #categories2 c2
14. ON c1.SpareNo = c2.SpareNo
15. AND c1.CategoryID = c2.CategoryId
16. WHERE c2.SpareNo IS NULL
17. ;
18. GO
19. --===== 3. FULL OUTER JOIN method
20. SELECT c1.*
21. FROM #categories1 c1
22. FULL JOIN #categories2 c2
23. ON c1.SpareNo = c2.SpareNo
24. AND c1.CategoryID = c2.CategoryId
25. WHERE c2.SpareNo IS NULL
26. ;
27. GO
+1