ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 251.8k

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

Feb 15 2021 1:23 PM

I work on SQL server 2012 I have issue I can't get Part Id that have only map code type from and code value from

and not have map code type to and code value to depend on table #map ?

every part id must be found on table trades two times

first row for same part for map from

second row for same part for map to

meaning every part must exist two time

but if it exist as one time for part as map from and not have map to

then this

what I need to display because it not have map to

as example

parts 1410,1445 have map from code tyepfrom and codevalue from 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 from code type and code value

only and not have map to(code type to and code value to ) 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,1),  
  12. (1387,9090,13456,0),  
  13. (1387,7070,13000,1),  
  14. (1390,9090,13456,0),  
  15. (1390,7070,13000,1),  
  16. (1800,9095,13570,0),  
  17. (1800,7075,14000,1),  
  18. (1850,9095,13570,0),  
  19. (1850,7075,14000,1),  
  20. (1400,9090,13456,0),  
  21. (1410,9090,13456,0),  
  22. (1445,9095,13570,0),  
  23. (1485,9095,13570,0)  
  24.   
  25.   
  26.  create table #map  
  27.  (  
  28.  MapId int,  
  29.  CodeTypeFrom int,  
  30.  CodeTypeTo int,  
  31.  CodeValueFrom int,  
  32.  CodeValueTo int  
  33.  )  
  34.  insert into #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)  
  35.  values  
  36.  (3030,9090,7070,13456,13000),  
  37.  (3035,9095,7075,13570,14000)  

Expected Result

 
  1. PartId    CodeTypeId    Code    PartLevel  
  2.  1400    9090    13456    0  
  3.  1410    9090    13456    0  
  4.  1445    9095    13570    0  
  5.  1485    9095    13570    0  

Answers (1)