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

ahmed salah 3,216 Reputation points
2021-03-08T16:51:51.46+00:00

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  

75497-image.png

75535-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,326 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} vote

2 answers

Sort by: Most helpful
  1. Guoxiong 8,201 Reputation points
    2021-03-08T19:49:13.177+00:00

    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;
    
    1 person found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,581 Reputation points
    2021-03-09T03:07:41.9+00:00

    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:
    75559-image.png

    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.

    1 person found this answer helpful.
    0 comments No comments