How to make update status when at least one site assembly exist based on Revision Id?

,
1

I work on SQL server 2012 Query I face issue : I can't Update status when at Least one AssemblySiteId Record on temp table #rev

Matched temp table #location based on Revision Id .

as example

Expected Result is :

Revision Id Status

1900 Found

2000 Not Found

5000 Found

as Example 1900 status Will be Found because Revision Id 1900 on temp #rev equal LocRevisionId on temp #location

and AssemblySiteId on temp #rev equal locAssemblySiteId on temp #location

at least one

and status not found will be where at least one locAssemblySiteId not found on temp table #location but revision Id equal revision id and this case found on revision Id 2000

create table #rev
(
RevisionId int,
AssemblySiteId int,
Status nvarchar(200)
)
insert into #rev(RevisionId,AssemblySiteId)
values
(1900,200),
(2000,300),
(5000,800)
create table #location
(
locRevisionId int,
locAssemblySiteId int
)
insert into #location(locRevisionId,locAssemblySiteId)
values
(1900,200),
(1900,150),
(2000,290),
(2000,310),
(5000,800),
(5000,820)

so How to make that please?

1 Like
2

hi

i tried to do this .. hope this is what you are looking for ..

select 
       a.RevisionId
     , case when b.locAssemblySiteId is not null then 'Found' else 'Not Found' end   as Status
from 
    rev a 
	   left join  
	location b 
	   on 
	a.RevisionId = b.locRevisionId 
	   and 
	a.AssemblySiteId = b.locAssemblySiteId

go

image

3

thank it solved

4

Sorry I am not able to understand .. Please tell the Meaning ..

What I did .. it solved your problem
OR
You did on your own ( something else ) which solved

1 Like