2
Answers

How to get PartId from table #trades where it have parts that have onl

ahmed elbarbary

ahmed elbarbary
3y
585
1

How to get PartId from table #trades where it have parts that have only map from and not have map to depend on table #map ?

I work on SQL server 2012 I have issue I can't get Part Id that have only map

To and not have map From

depend on table #map ?

every part id must be found on table trades two times

first row for same part for map from code type from and code value from second row for same part for map to code type to and code value to

meaning every part must exist two time but if it exist as one time for part as map To code type and code value and not have map From code type and code value

then this

what I need to display because it not have map From

as example parts 1410,1445,1445,1485,1348,1850 have map To only so it must display

part 1348 no need to display or show because it have map from and map to

so How to write query on SQL server 2012 display parts from table trades that have map To only and not have map From depend on table #map ?

  1. create table #trades  
  2.  (  
  3.  PartId int,  
  4.  CodeTypeId int,  
  5.  Code int,  
  6.  PartLevel int  
  7.  )  
  8.  insert into #trades(PartId,CodeTypeId,Code,PartLevel)  
  9.  values  
  10.  (1348,9090,13456,0),  
  11.  (1348,7070,13000,0),  
  12.  (1387,9090,13456,0),  
  13.  (1387,7070,13000,0),  
  14.  (1390,9090,13456,0),  
  15.  (1390,7070,13000,0),  
  16.  (1800,9095,13570,0),  
  17.  (1800,7075,14000,0),  
  18.  (1850,9095,13570,0),  
  19.  (1850,7075,14000,0),  
  20.  (1400,7070,13000,0),  
  21.  (1410,7070,13000,0),  
  22.  (1445,7075,14000,0),  
  23.  (1485,7075,14000,0),  
  24.  (1348,7075,14000,0),  
  25.  (1850,7070,13000,0)  
  26.       
  27.   create table #map  
  28.   (  
  29.   MapId int,  
  30.   CodeTypeFrom int,  
  31.   CodeTypeTo int,  
  32.   CodeValueFrom int,  
  33.   CodeValueTo int  
  34.   )  
  35.   insert into #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)  
  36.   values  
  37.   (3030,9090,7070,13456,13000),  
  38.   (3035,9095,7075,13570,14000)  
  39.   
  40.   
  41. expected result  
  42.   
  43.  TradeCodesId    PartId    CodeTypeId    Code    PartLevel  
  44.  11    1400    7070    13000    0  
  45.  12    1410    7070    13000    0  
  46.  13    1445    7075    14000    0  
  47.  14    1485    7075    14000    0  
  48.  15    1348    7075    14000    0  
  49.  16    1850    7070    13000    0
Answers (2)
Next Recommended Forum