Joining Three Item Master tables where one table may have similar values in UOM Schedule table

[awaria]

SQL Server 2000 SP2

Working an a query to extract item master data, where tables include item master Table A, item location Table B, item UOM Table C, all to be joined together.

All item have one master record, so (1) record in Table A
All items have (3) locations, so three records in Table B.
All tems have (2) UOMs. Sometimes the upms are different, sometimes there are the same.
*ex: item "ABC" has 2 uoms, Each and Case
item "DEF" has 2 uoms, Each and Each

Desired result should (6) records, not (12), (3) for each item # by Location 1, 2, 3, with the two UOMs displayed in separate columns;
ITEM# LOCATION UOM1 UOM2
ABC Loc 1 EACH Case
ABC Loc 2 EACH Case
ABC Loc 3 EACH Case
DEF Loc 1 EACH EACH
DEF Loc 2 EACH EACH
DEF Loc 3 EACH EACH

Challenge is with items that have the same UOM for both uom schedules, such as item "DEF" above.

There is a numeric key field in the UOM Table, Table C, that is always least for the first record, so I thought I could use that value to determine which value populates Columns UOM1 and UOM2.

However, I don't know how to programtically do that.

Appreciate help in accomplishing this query and results.

Thanks,

awaria

 

[SkipVought]

hi,

Working an a query...

WHAT query?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gk53]

Look on something like that

declare @tableA as table
 (
	item varchar(5)
 )
 
declare @tableB as table
 (
	item varchar(5),
	location varchar(10)
 )
 
declare @UOM as table
 (
	item varchar(5),
	UOM varchar(10)
 )
 
 insert into @tableA
 select 'ABC'
 union 
 select 'DEF'
 
 insert into @tableB
 select 'ABC', 'Loc 1'
 union
 select 'ABC', 'Loc 2'
 union
 select 'ABC', 'Loc 3'
 union 
 select 'DEF', 'Loc 1'
 union
 select 'DEF', 'Loc 2'
 union
 select 'DEF', 'Loc 3'
 
 
insert into @UOM
select  'ABC', 'EACH'
union all
select  'ABC', 'Case'
union all
select  'DEF', 'EACH'
union all
select  'DEF', 'EACH'

;with t as
(
	select ROW_NUMBER() over(partition by item order by  UOM) row, item, uom
		from @UOM 
		group by item, uom
)
select a.item, b.location, t1.UOM--, t2.UOM
from @tableA a
join @tableB b
	on a.item = b.item
left join t as t1
	on t1.item = a.item
	and t1.row = 1
left join t as t2
	on t2.item = a.item
	and t2.row = 2

 

[SkipVought]

So it would seem that before joining anything with UOM, you need to Select DISTINCT, that would result in all unique rows in the resultset and would prevent the duplication that seem to be troubling you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]