How to get partvalue of #temp where same chemicalid and same partid exist on two tables ?

Question

0.00/5 (No votes)

See more:

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

Posted 26-May-22 13:02pm

ahmed_sa

Updated 28-May-22 1:17am

Add a Solution

Comments

OriginalGriff 27-May-22 0:49am

This is your 385th question: you should know by now (better than anyone else) how to ask a question!

What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ahmed_sa · Accepted Answer · 2022-05-28T01:17:00

Solution 1

this solve my issue

SELECT ch.partid,ch.chemicalid,Coalesce(t2.partvalue,ch.partvalue)  partvalue
FROM #chemicals ch
inner join #temp t on t.chemicalid=ch.chemicalid
left join #temp t2 on t2.partid=ch.partid

thank you for help and support

Posted 28-May-22 1:17am

ahmed_sa

How to get partvalue of #temp where same chemicalid and same partid exist on two tables ?

1 solution

Solution 1

Add your solution here

Preview 0

How to get partvalue of #temp where same chemicalid and same partid exist on two tables ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us