Select record that does not exist in another table in SQL Server

Hi ahmedsa,

You can use JOIN query for this or use WHILE loop.

With WHILE loop

CREATE TABLE #trades
(
	TradeCodesId INT IDENTITY(1,1),
	PartId INT,
	CodeTypeId INT,
	Code INT,
	PartLevel INT
)

INSERT INTO #trades(PartId,CodeTypeId,Code,PartLevel)
VALUES
(1348,9090,13456,0),(1348,7070,13000,0),
(1387,9090,13456,0),(1387,7070,13000,0),
(1390,9090,13456,0),(1390,7070,19000,0),
(1800,9095,13570,0),(1800,7075,28000,0),
(1850,9095,13570,0),(1850,7075,74000,0)
         
CREATE TABLE #map
(
	MapId INT,
	CodeTypeFrom INT,
	CodeTypeTo INT,
	CodeValueFrom INT,
	CodeValueTo INT
)
INSERT INTO #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)
VALUES
(3030,9090,7070,13456,13000),
(3035,9095,7075,13570,14000)


CREATE TABLE #trades_Out
(
	TradeCodesId INT,
	PartId INT,
	CodeTypeId INT,
	Code INT,
	PartLevel INT
)


DECLARE @Counter INT, @TotalCount INT
SET @Counter = 1
SET @TotalCount = (SELECT COUNT(*) FROM #trades)

WHILE (@Counter <= @TotalCount)
BEGIN
	DECLARE @CodeTypeId INT, @Code INT

	SET @CodeTypeId = (SELECT CodeTypeId FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #trades) t WHERE t.RowId = @Counter)
    SET @Code = (SELECT Code FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #trades) t WHERE t.RowId = @Counter)

	IF NOT EXISTS (SELECT * FROM #map WHERE CodeTypeFrom = @CodeTypeId AND CodeValueFrom = @Code)
	BEGIN
		IF NOT EXISTS (SELECT * FROM #map WHERE CodeTypeTo = @CodeTypeId AND CodeValueTo = @Code)
		BEGIN
			INSERT INTO #trades_Out (TradeCodesId,PartId,CodeTypeId,Code,PartLevel)
			SELECT TradeCodesId,PartId,CodeTypeId,Code,PartLevel 
			FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #trades) t WHERE t.RowId = @Counter
		END
	END
	
    SET @Counter = @Counter  + 1
	CONTINUE;
END

SELECT * FROM #trades_Out

DROP TABLE #trades
DROP TABLE #map
DROP TABLE #trades_Out

With JOIN

CREATE TABLE #trades
(
	TradeCodesId INT IDENTITY(1,1),
	PartId INT,
	CodeTypeId INT,
	Code INT,
	PartLevel INT
)

INSERT INTO #trades(PartId,CodeTypeId,Code,PartLevel)
VALUES
(1348,9090,13456,0),(1348,7070,13000,0),
(1387,9090,13456,0),(1387,7070,13000,0),
(1390,9090,13456,0),(1390,7070,19000,0),
(1800,9095,13570,0),(1800,7075,28000,0),
(1850,9095,13570,0),(1850,7075,74000,0)
         
CREATE TABLE #map
(
	MapId INT,
	CodeTypeFrom INT,
	CodeTypeTo INT,
	CodeValueFrom INT,
	CodeValueTo INT
)
INSERT INTO #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)
VALUES
(3030,9090,7070,13456,13000),
(3035,9095,7075,13570,14000)

SELECT t.TradeCodesIdFrom, t.PartIdFrom, t.CodeTypeFrom, t.CodeValueFrom, 
    t.TradeCodesIdTo, t.PartIdTo, t.CodeTypeTo, t.CodeValueTo, t.PartLevel,
    m.CodeValueFrom AS MapCodeValueFrom, m.CodeValueTo AS MapCodeValueTo
INTO #temp 
FROM 
(
	SELECT t1.TradeCodesId AS TradeCodesIdFrom, 
		t1.PartId AS PartIdFrom, 
		t1.CodeTypeId AS CodeTypeFrom, 
		t1.Code AS CodeValueFrom, 
		t2.TradeCodesId AS TradeCodesIdTo,
		t2.PartId AS PartIdTo,
		t2.CodeTypeId AS CodeTypeTo, 
		t2.Code AS CodeValueTo,
		t1.PartLevel
	FROM #trades t1
	JOIN #trades t2 ON t1.CodeTypeId != t2.CodeTypeId AND t1.PartId = t2.PartId
) t
JOIN #map m ON (t.CodeTypeFrom = m.CodeTypeFrom AND t.CodeTypeTo = m.CodeTypeTo)
AND (t.CodeValueFrom != m.CodeValueFrom OR t.CodeValueTo != m.CodeValueTo )

SELECT TradeCodesIdFrom 'TradeCodesId', PartIdFrom 'PartId', CodeTypeFrom 'CodeTypeId', CodeValueFrom 'Code',PartLevel
FROM #temp WHERE CodeValueFrom != MapCodeValueFrom
UNION ALL
SELECT TradeCodesIdTO, PartIdTo, CodeTypeTo, CodeValueTo,PartLevel 
FROM #temp WHERE CodeValueTo != MapCodeValueTo

DROP TABLE #trades
DROP TABLE #map
DROP TABLE #temp

Output