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

Question

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  

Answer 1

Try this:

SELECT t.*
FROM #trades AS t
INNER JOIN (
    SELECT t.*
    FROM #trades AS t
    INNER JOIN #map AS m ON t.CodeTypeId = m.CodeTypeFrom AND t.Code = m.CodeValueFrom
) AS x 
    ON x.PartId = t.PartId
INNER JOIN #map AS m 
    ON t.CodeTypeId = m.CodeTypeTo AND t.Code <> m.CodeValueTo;

Answer 2

Hi @ahmed salah ，

Please refer to:

SELECT  t.*   
FROM #trades t  
JOIN #map m  
ON t.CodeTypeId=m.CodeTypeTo and t.Code<>m.CodeValueTo  
WHERE PartId in (SELECT t.PartId FROM #trades t  
                JOIN #map m ON t.CodeTypeId=m.CodeTypeFrom AND t.Code=m.CodeValueFrom)  

Output:

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.