Select records from table that do not exist in another table in SQL Server

ahmedsa
 
on Mar 29, 2021 11:31 PM
550 Views

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

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 30, 2021 07:58 AM

I will get back to you tomorrow.

dharmendr
 
on Mar 31, 2021 04:14 AM

Hi ahmedsa,

Use NOT EXISTS to check with where clause.

Refer below query.

SQL

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 #categories2(SpareNo,CategoryId)
VALUES
(1200,5050), (1200,5090), (1200,5070), (1350,7090), (1350,4020),
(1700,8612), (1990,7575), (1990,2050), (7000,4200), (7000,4500)

SELECT c1.catId,c1.SpareNo,c1.CategoryId
FROM #categories1 c1 
WHERE NOT EXISTS 
(
	SELECT c2.catId,c2.SpareNo,c2.CategoryId
	FROM #categories2 c2   
	WHERE c1.SpareNo = c2.SpareNo AND c1.CategoryID = c2.CategoryId
) 

DROP TABLE #categories1
DROP TABLE #categories2

Output

catId SpareNo CategoryId

    2       1350         5902

    3       1700         8070

    5       7000         2030