Troy Bryant Troy Bryant - 4 months ago 8
SQL Question

Sql query to find replacement items

Not the greatest with sql but I am struggling with writing this query. The situation is I have an item table. If the item is obsolete 'obso' and has a replacement ID then in a query I should select that replacement item rather than the already obsoleted one. Here is what i have and a few pics to make it clearer.

select
o.ReplacementItemID,
o.ItemStatus,
o.Description,
o.ItemWarrantyID,
o.Id as OriginalItemId
from
(
select
id,
ItemStatus,
Description,
ItemWarrantyID,
ReplacementItemID
from item
where ItemStatus = 'obso'
)o
inner join Item i
on o.ReplacementItemID = i.Id


Which gives me enter image description here

which is incorrect the warranty id should be 110 like the two examples below:

enter image description here

Answer

As sgeddes mentioned in the comments you are referencing the wrong ItemWarrantyID. Your...

from
(
select 
    id,
    ItemStatus,
    Description,
    ItemWarrantyID,
    ReplacementItemID
from item
where ItemStatus = 'obso'   
)o

Is all about the original. The part after that...

inner join Item i
on o.ReplacementItemID = i.Id

Makes it so you can only see parts who have a replacement. The issue is you only utilize the original while filtering out all of the rows that don't have a replacement.

So your...

select 
o.ReplacementItemID,
o.ItemStatus,
o.Description,
o.ItemWarrantyID,
o.Id as OriginalItemId

Should be...

select 
o.ReplacementItemID,
o.ItemStatus,
o.Description,
i.ItemWarrantyID,
o.Id as OriginalItemId

Assuming you are only looking for originals (which it seems like you are based off of the where ItemStatus = 'obso' and your inner join and many other things)

Comments