How to write query return status conflict if mask related parts not have same checmicalid ?

Question

0.00/5 (No votes)

See more:

SQL

create table #temp
(
partid     int,
maskid     int,
chemicalid int,
status  nvarchar(50)
)
insert into #temp
values
(100,1000,901)
(700,1700,909)
(400,1500,920)
Parts   
pk       
PartId    maskId
100       1000
200       1000
400       1500
600       1700
700       1700

Chemical Master Table
   PK                          fk
Chemicalmasterid  ChemicalId  PartId
  1                901         100
  2                902         200
  4                920         400
  6                909         600
  7                909         700

I need to write query do

update status='conflict chemical id' where
another parts related to same mask on temp table #temp have different checmicalid

for explain

#temp table have part 100 for masked 1000 and checmicalId 901
I will search on database

Are mask number 1000 have related parts ON Table parts have same maskId ?

yes it have

what other parts have same maskId ?

200 have maskid 1000

Are these parts as 200 have same chemicalid as exist on temp table #temp for same maskId ?

No then status will be 'conflict checmical id '

status conflict because maskId 1000 have chemicalId 901 on temp table #temp but on table parts
related parts have same maskId have different chemicalId as 902

What I have tried:

finalResult
100 1000 901  conflict chemicalid
700 1700 909
400 1500 920

Posted 8-Jan-20 13:45pm

ahmed_sa

Updated 5-Mar-20 12:04pm

v2

Add a Solution

Comments

Maciej Los 9-Jan-20 2:28am

Stop asking the same question several times!

ahmed_sa 9-Jan-20 2:50am

thank you for reply this is not same question
if i ask more question on one question it will difficult for understand

ZurdoDev 9-Jan-20 7:48am

Reply to the comment so that the user is notified.

1 solution

Add a Solution

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

MarcusCole6833 · Accepted Answer · 2020-03-05T12:04:00

declare @t table
(
partid     int,
maskid     int,
chemicalid int,
status  nvarchar(50)
)
insert into @t
(partid, maskid, chemicalid )
values
(100,1000,901),
(700,1700,909),
(400,1500,920)

declare @p table
(
partid int, maskid int
)
insert into @p
(partid, maskid )
values
(100,1000),
(200,1000),
(400,1500),
(600, 1700 ),
(700, 1700)

;with t as 
(
	select p1.partid from @p p1
	inner join @p p2 on p1.maskid = p2.maskid and p1.partid != p2.partid
	
)

update t1
set [status] = 'conflicted'
from @t t1 
inner join t  t2
on t1.partid = t2.partid 


select * from @t

I would look up self joins

sql - What is SELF JOIN and when would you use it? - Stack Overflow[^]

How to write query return status conflict if mask related parts not have same checmicalid ?

1 solution

Solution 1

Add your solution here

Preview 0

How to write query return status conflict if mask related parts not have same checmicalid ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us