Loading

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

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

ahmed elbarbary

5y720

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 :

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Kiran Mohanty5y ago
I guess your query should look like below code
select * from #partattributes ps
inner join #tradecodecontrol tc on ps.FeatureKey=tc.FeatureKey
inner join #tradecodecontrol tc1 on tc.Code = tc1.Code
0