SQLTeam.com | Weblogs | Forums

Return the same value based on two different values


#1

Good afternoon,

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

     95 41190    40275           2888 20000    12138
     95 41190    40275           9105 14500    00438
   7176 41190    00212          16241 38900    04205
   7132 41190    00041          13334 44000    00550
   7176 41190    00212          15989 41224    64006
  76590 41190    00014           3966 24100    13067
  76590 41190    00014           9308 44000    00057

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

 95   41190    40275   descriptionA   2888 20000    12138   descriptionB

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

Any help would be greatly appreciated!
Thanks
S


#2

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

#3

Hi James, thank you!!
I added in descriptionA and descriptionB to the desired output....
Right now with that original query I run I get this output:"

description QUAKER CHEWY CHOC CHIPS
sr_item_key 29568
sr_mcode 41190
sr_icode 04652
nb_item_key 101108
nb_mcode 30000
nb_icode 31182
description QUAKER CHEWY CHOC CHIPS
pack 12
sizes 6.7

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

Cheers.. Steve


#4

What is IFX? I am not familiar with it. The query I posted earlier should work on Microsoft SQL Sever 2005 or later.


#5

It's Informix


#6

This is a Microsoft SQL Server forum. There are few, if any, on this forum who would be able to help you with Informix questions.