locked
How to get parts related to every Code by Features related ? RRS feed

  • Question

  • User696604810 posted

    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 .

    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 ?

    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)

    Expected Result :
    
    
        PartId FeatureKey Zplid CodeTypeId Code FeatureKey
        17890 1502260016 25820 849774 8536698000 1502260016
        17890 1502260016 25820 849774 8536698000 1502260001
        17830 1502260016 25820 849774 8536698000 1502260016
        17830 1502260016 25820 849774 8536698000 1502260001
        17705 1502260016 25820 849774 8536698000 1502260016
        17705 1502260016 25820 849774 8536698000 1502260001
        17910 1502260016 25820 849774 8536698000 1502260016
        17910 1502260016 25820 849774 8536698000 1502260001
        17880 1502260016 25820 849774 8536698000 1502260016
        17880 1502260016 25820 849774 8536698000 1502260001
    Tuesday, December 29, 2020 2:20 AM

All replies

  • User-939850651 posted

    Hi ahmedbarbary,

    According to your description, do you want to get the  Code in the first table ( #tradecodecontrol ) based on the FeatureKey in the second table( #partattributes ), and then query all the data related to this Code?

    If this is the case, you could directly obtain the Cartesian product of the two tables, and then filter the data set.

    Something like this ( 1502260016  is paramter) :

    select * from #partattributes,#tradecodecontrol 
    where Code = IIF((select top 1 FeatureKey from #partattributes where FeatureKey = 1502260016) <> '',
    		(select top 1 code from #tradecodecontrol where FeatureKey = 1502260016), '')
    order by PartId

    Or do you want to get all the Code based on the two tables and query related data?

    If this is the case, you could try something like this:

    select * from #partattributes,#tradecodecontrol 
    where Code in (select Code from #partattributes pt join #tradecodecontrol tc on pt.FeatureKey=tc.FeatureKey)
    order by PartId

    Result:

    But if I misunderstood something, could you describe your requirements more clearly?

    Best regards,

    Xudong Peng

    Tuesday, December 29, 2020 7:35 AM
  • User696604810 posted

    thank you for reply

    result is correct

    but i need it to be dynamic way

    select * from #partattributes,#tradecodecontrol 
    where Code = IIF((select top 1 FeatureKey from #partattributes where FeatureKey = 1502260016) <> '',
    		(select top 1 code from #tradecodecontrol where FeatureKey = 1502260016), '')
    order by PartId

    I need to get 1502260016 by dynamic way
    meaning how to get value of feature key without write on SQL query as static
    Tuesday, December 29, 2020 7:49 AM
  • User-939850651 posted

    Hi ahmedbarbary,

    I modified part of the data and modified the sql query statement for testing.

    I think you might want to achieve something like:

    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',1502260002),
        (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,1502260001),
        (17705,1502260016),
        (17910,1502260016),
        (17880,1502260016)
    
      select A.PartId,A.AKey FeatureKey,A.Zplid,A.CodeTypeId,A.Code,B.FeatureKey 
      from 
    	(select pt.PartId,pt.FeatureKey AKey,tc.Zplid,tc.CodeTypeId,tc.Code,tc.FeatureKey 
    		from #partattributes pt join #tradecodecontrol tc on pt.FeatureKey=tc.FeatureKey
    	) A 
    	join #tradecodecontrol B on B.Code = A.Code 
      order by A.PartId,A.Code

    For example, when FeatureKey is 1502260016, it corresponds to one Code ( 8536698000 ). When FeatureKey is 1502260001, it corresponds to two Code  ( 8536698000 and 8538908180 ).

    So the result should be:

    Hope this can help you.

    Best regards,

    Xudong Peng

    Tuesday, December 29, 2020 10:36 AM