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

Transact-SQL
, ,
1

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
2

What you tried does not work, something is missing? oops sorry, it works!

try this

select * from @tradecodecontrol tc
    left join @partattributes ps   on ps.FeatureKey=tc.FeatureKey
3

not same result

4 
SELECT pa.PartId, tcc.FeatureKey
FROM #partattributes pa
CROSS JOIN (
    SELECT DISTINCT FeatureKey
    FROM #tradecodecontrol
    WHERE FeatureKey IS NOT NULL
) AS tcc
ORDER BY PartId, FeatureKey
5

thank you very much

Not Exactly what i need

result is OK

but i need to do cross join to specific code

code 8536698000

as example part 17890 have feature key 1502260016 related to code 8536698000

so i will search inside tradecodecontrol table to features related to code

after that I will get features related then make cross join to features related to code

but above solution make cross join for table where feature key is not null

and must also get it with Code also

cross join where featurekey is not null related to code on trade code control

so How to do that Please ?

6

hi Ahmed .. long long long time hello

i tried to do this .. hope this helps ...

hope this works in all DATA scenarios
well know fact data can break SQL very easily

select 
	      a.Code
		, a.FeatureKey 
    from 
	   (select code , FeatureKey from #tradecodecontrol	where code in ( select  code  from #tradecodecontrol  a where exists ( select code from #partattributes b where b.FeatureKey = a.FeatureKey )  ) ) 
	   a 
	join 
	   #partattributes b 
	on 1=1

image

7

NO reply Ahmed

Yes / No / Maybe

8

yes it ok thank you very much