How to get parts related to every Code by Features related ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  December 29, 2020 at 2:17 am

  Go to Answer

  #3824941

  I work on SQL server I face issue I can't display features related to every part by code

  so part attribute table have parts with feature key 1502260016

  and feature key 1502260016 related to code 8536698000

  and code have 2 feature 1502260016 and 1502260001

  so part must have two features or two rows not one row .

    create table #tradecodecontrol
      (
      Zplid int,
      CodeTypeId int,
      Code nvarchar(20),
      FeatureKey  int
      )
      insert into #tradecodecontrol(Zplid,CodeTypeId,Code,FeatureKey)
      values
      (25820,854838,'EAR99',NULL),
      (25820,849774,'8538908180',1502260001),
      (25820,849774,'8536698000',1502260001),
      (25820,849774,'8536698000',1502260016),
      (25820,849774,'8536694040',NULL)
      
      CREATE table #partattributes
      (
      PartId int,
      FeatureKey  int
      )
      insert into #partattributes (PartId,FeatureKey)
      values
      (17890,1502260016),
      (17830,1502260016),
      (17705,1502260016),
      (17910,1502260016),
      (17880,1502260016)
  
  what I try is :
  
      select * from #partattributes ps 
      inner join #tradecodecontrol tc on ps.FeatureKey=tc.FeatureKey

   

  it display 5 rows for 5 parts as one Feature per Part

  but exactly I need to get features related to every code so I need to display 2 Feature per every part

  because feature key 1502260016 related to code 8536698000

  and code 8536698000 have two features 1502260016 and 1502260001

  so every part must have two features

  this meaning total rows per 5 parts will be 10 rows as two feature per every part as below:

  so how to get that please by sql query ?

  Expected Result :
  
      PartIdFeatureKeyZplidCodeTypeIdCodeFeatureKey
      1789015022600162582084977485366980001502260016
      1789015022600162582084977485366980001502260001
      1783015022600162582084977485366980001502260016
      1783015022600162582084977485366980001502260001
      1770515022600162582084977485366980001502260016
      1770515022600162582084977485366980001502260001
      1791015022600162582084977485366980001502260016
      1791015022600162582084977485366980001502260001
      1788015022600162582084977485366980001502260016
      1788015022600162582084977485366980001502260001

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  December 29, 2020 at 9:30 am

  Answer

  #3825033

  Maybe this?

      select ps.*,tc2.* from #partattributes ps 
      inner join #tradecodecontrol tc on ps.FeatureKey=tc.FeatureKey
      inner join #tradecodecontrol tc2 on tc2.Code=tc.Code

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537