I have a table that links two different items by a unique item_key
I am trying to get the description that is held in the 'items' table for each value in the cross reference and display it side by side. The cross reference table is it_xref and the item_key is the primary key in the 'items' table.
Can anyone assist in having this 'description' displayed for each cross reference field?
Example of the it_xref table:
sr_item_key sr_mcode sr_icode nb_item_key nb_mcode nb_icode
I am looking to reference the description field in table: items based on sr_item_key value 95 and nb_item_key 2888
in the items table both sr_item_key and nb_item_key = item_key field
so
items.item_key 95 description
items.item_key 2888 description
would both show in the output such as
sr_item_key sr_mcode description sr_icode nb_item_key nb_mcode nb_icode description
Query I have only shows the single description, not too sure of the correct way to handle this one.
select t.description,
i.sr_item_key,
i.sr_mcode,
i.sr_icode,
i.nb_item_key,
i.nb_mcode,
i.nb_icode,
t.description,
t.pack,
t.sizes
from it_xref i, items t
where i.nb_item_key = t.item_key
or i.sr_item_key = t.item_key
The output you have shown and the query do not seem to match - different number of columns etc. So this is just an educated guess as to what you are looking for. if this is not it, can you post the details of the items table as well, and the exact output you are looking for?
select
i.*,
t.description,
t2.descripton
from
it_xref i
outer apply
(
select top (1) t.description
from items t
where i.nb_item_key = t.item_key
) as t
outer apply
(
select top (1) t2.description
from items t2
where i.sr_item_key = t2.item_key
) as t2
What I am looking for is the description from each of the sr_item_key and nb_item_key
in the items table they are both referenced simply as item_key
So:
description QUAKER CHEWY CHOC CHIPS
sr_item_key 29568
in the item file this is the description for item_key = 29568
I also want to pull the description for item_key = 101108
(the cross referenced item)
Thanks..
I did attempt to run your query and came back with a syntax error
Should have mentioned this is on IFX