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

ahmedsa
 
on Mar 08, 2021 10:32 PM
855 Views

How to get PartId from table #trades that have wrong Code ON Code Value To when code type from and code value from Exist?

I work on SQL server 2012 I have issue I can't get Part Id and code type that have different on Code

On table #trades and table map code value to for same code type depend on table #map ?

So firstly get part Id and code type from and code value from must exist on table #trades

Then check same partid and code value to and code type to and get code value different when Code type to on table map equal code type exist on table #trades

If code value to not same as table trades code then display it

as Example steps

1- I get from table map code type from 9090 and Code Value from 13456

2- then i will go to table #trade code search for code type 9090 and code value 13456

So i found partid and code type from 9090 and code value from 13456 for partid 1390 exist

3- then check mapped code type to and map code value to

If this part have same code type to and different on code value to

Then display it so part id 1390 must display

Because code type from 9090 and code value from 13456 exist and code type to 7070 exist but code value to on map table that has value 13000 not equal 19000

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)

Expected result

TradeCodesId PartId CodeTypeId Code PartLevel
6            1390    7070      19000  0
8            1800    7075      28000  0
10           1850    7075      74000  0

 

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

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

TradeCodesId PartId CodeTypeId Code PartLevel
6 1390 7070 19000 0
8 1800 7075 28000 0
10 1850 7075 74000 0