Click here to Skip to main content
14,639,515 members
Rate this:
Please Sign up or sign in to vote.
I work on SQL server 2012 I face issue I can't get Active Parts for Obsolete Parts

create table #Replacement
(
PartIDC  INT,
PartIDX  INT
)
insert into #Replacement
(PartIDC,PartIDX)
values 
(1222,3421),
(3421,5643),
(5643,2243),
(2243,3491)

create table #LifeCycleMaster
(
ZpartId  int,
Zlc  int
)
insert into #LifeCycleMaster
(ZpartId,Zlc)
values
(1222,2000),
(3421,2000),
(5643,2000),
(2243,2000),
(3491,2001)
Create table #acceptedvalues
(
acceptedvaluesid int,
acceptedvaluesname nvarchar(50)
)
insert into #acceptedvalues
values
(2000,'Obsolete'),
(2001,'Active')


Result :

PartIDX   PartIDC
1222      3491


I get first Part Obsolete so when get first Part Obsolete will be 1222

then I search for active Part for obsolete Part 1222 on Part X i found it not on same row

but I found it on row number 4 and value on it on Part X as 3491

so How to do that ?

What I have tried:

select r1.PartIDC,r2.PartIDX from #Replacement r1
inner join #Replacement r2 on r1.PartIDC=r2.PartIDX
inner join #LifeCycleMaster m on m.ZpartId=r1.PartIDX
where m.Zlc=2001
Posted
Updated 12-Sep-20 15:51pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You're joining #replacement with itself

select r1.PartIDC,r2.PartIDX from #Replacement r1
inner join #Replacement r2 on r1.PartIDC=r2.PartIDX
inner join #LifeCycleMaster m on m.ZpartId=r1.PartIDX
where m.Zlc=2001
   
Comments
ahmed_sa 13-Sep-20 2:50am
   
thank you for reply
Expected result is :
PartIDC PartIDX
1222 3491
but i get it wrong as
PartIDC PartIDX
2243 2243
when apply answer above
can you help me get correct answer
Gerry Schmitz 13-Sep-20 10:50am
   
Try something. I showed you what's wrong.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100