I work on sql server 2017 i face issue i need to get all parts related to chemicalid exist on two table #temp and #chemical
on case of part id related to same chemical on two table and same part exist on two table
then i will get partvalue of #temp not on #chemical
suppose i have same chemical on both table as 1900 and same part for both table as 121 then take value of partvalue of #temp table as AX
create table #temp
(
partid int,
chemicalid int,
partvalue VARCHAR(10)
)
insert into #temp(partid,chemicalid,partvalue)
values
(121,1900,'AX'),
(231,1885,'MX')
create table #chemicals
(
partid int,
chemicalid int,
partvalue VARCHAR(10)
)
insert into #chemicals(partid,chemicalid,partvalue)
values
(121,1900,'AXO'),
(304,1900,'MDO'),
(220,1900,'LGO'),
(306,1900,'HFK'),
(231,1885,'MXN'),
(550,1885,'RGK'),
(770,1885,'LFH'),
(550,1885,'OPT')
What I have tried:
SELECT ch.partid,ch.chemicalid,ch.partvalue FROM #chemicals ch
inner join #temp t on t.chemicalid=ch.chemicalid