Linking Tables: Legacy to SQL 1

[Memento]

I have a legacy database that used the old "Numbering" method to store values.

ie. Item1, Item2, Item3, Item4, and Item5

I imported the old flat-file database into SQL Server 2000. I have an Items and Orders table. The Items table has the PK - ItemID, and has all the info on the items. The Orders Table has the PK-ItemsID stored in the Item1, Item2, Item3, Item4, and Item5 fields in the Orders table.

I can't figure out how to link the two tables together. I can't link the ItemID to ALL five Item1-5 otherwise it doesn't return anything.

I would like to pull all the Item1-5 and return their descriptions.

 

[vongrunt]

> I can't link the ItemID to ALL five Item1-5 otherwise it doesn't return anything.

You can. Simply create five [!]outer[/!] joins on Items table.

Since this is legacy DB I won't preach about 1NF .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Memento]

vongrunt,

I abhor the use of numbering fields to hold values. Whenever I see it, I know this person either has little knowledge of database design or was limited by software.

When I run the LEFT OUTER join, it only returns the Item tables. As it is now, I could completely leave out the orders table all together and it would return the same data.

For simplicities sake, here's a clip of tables:

Item Table
ItemID ItemDesc ItemFrom ItemTo
BC90801 Staples 1 5
BC90802 Paper Clips 1 5
BC90803 Tape 6 10

Orders Table
BuyerID Item1 Item2 Item3 Item4 Item5
101 BC90801 BC90803
101 BC90802 BC90803 BC90801

Goal:
BuyerID Item1 ItemDesc ItemFrom ItemTo Item2 ItemDesc ItemFrom ItemTo
101 BC90801 Staples 1 5 BC90803 Tape 6 10

Next Record
BuyerID Item1 ItemDesc ItemFrom ItemTo Item2 ItemDesc ItemFrom ItemTo Item3 ItemDesc ItemFrom ItemTo
101 BC90802 Paper Clips 1 5 BC90803 Tape 6 10 BC90801 Staples 1 5

 

[vongrunt]

Sample data (real tables, test it on sandbox DB):

create table Items (ItemID char(7), ItemDesc varchar(32), ItemFrom int, ItemTo int )
insert into Items values ('BC90801', 'Staples'    , 1, 5 )
insert into Items values ('BC90802', 'Paper Clips', 1, 5 )
insert into Items values ('BC90803', 'Tape'       , 6, 10)

create table Orders(BuyerID int, Item1 char(7), Item2 char(7), Item3 char(7), Item4 char(7), Item5 char(7))
insert into Orders values (101, 'BC90801', 'BC90803', null, null, null)
insert into Orders values (101, 'BC90802', 'BC90803', 'BC90801', null, null)

Query:

select O.BuyerID, 
	O.Item1, I1.ItemDesc as ItemDesc1, I1.ItemFrom as ItemFrom1, I1.ItemTo as ItemTo1,
	O.Item2, I2.ItemDesc as ItemDesc2, I2.ItemFrom as ItemFrom2, I2.ItemTo as ItemTo2,
	O.Item3, I3.ItemDesc as ItemDesc3, I3.ItemFrom as ItemFrom3, I3.ItemTo as ItemTo3,
	O.Item4, I4.ItemDesc as ItemDesc4, I4.ItemFrom as ItemFrom4, I4.ItemTo as ItemTo4,
	O.Item5, I5.ItemDesc as ItemDesc5, I5.ItemFrom as ItemFrom5, I5.ItemTo as ItemTo5
from Orders O
left outer join Items I1 on O.Item1=I1.ItemID	
left outer join Items I2 on O.Item2=I2.ItemID
left outer join Items I3 on O.Item3=I3.ItemID
left outer join Items I4 on O.Item4=I4.ItemID
left outer join Items I5 on O.Item5=I5.ItemID
-- order by O.whatever

Yup, not something for beauty contest

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Memento]

Simply amazing! Everything worked as you stated.

You've been a great help. I seriously beleive you have raised my mental health.