Complicated Lookup Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Complicated Lookup Table

Author

Topic

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-14 : 10:55:46

Hello All

Thank you in advance for any help I can get with this. Up until this point I have developed products with fairly simple straightfoward. My experience with MSSQL is limited to this straightforward type design so when I am presented with something like this I am at a loss. It looks like my project is going to require a fairly complex process involving a lookup table.

Here is the situation: I have a database which is comprised totally of data that is bulk inserted from text files which I get from another company.

The layout we have is as follows:

TABLE 1
ID   features1   features2
-------------------------------------------
5   2,5,8,   1,3,5,
6   1,2,       7,3,

LOOKUP TABLE
ID feature
--------------------------------
1 brown
2 red
3 green
4 blue
5 yellow
6 pink
7 black
8 orange

I currently have a stored procedure that I feed the record ID and it returns the whole record with the feature ID's. Like this:

6 1,2, 7,3,

I would like the stored procedure to first preform the lookup against the lookup table and then return the english feature like this:

6 brown, red black, green

Is there a way to do this? I can't figure this out. If anyone can help I would be very forever indebted. Thanks again

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-14 : 11:49:31

This looks like a case of poor design. You need to normalize these tables.

You should have a table with
ID FeatureID
------------
5 2
5 5
5 8
5 1
5 3
5 5
6 1
6 2
6 7
6 3

Then it would just be a simple join like:

SELECT ID, Feature
FROM Table1 t1 INNER JOIN Lookup l
ON t1.FeatureID = l.FeatureID

HTH
-Chad

lfmn
Posting Yak Master

141 Posts

Posted - 2002-02-14 : 12:04:07

step 1 - fire your database designer

in the meantime, this UGLY query should help you build a stored procedure to do what you need.

--this code recreates your scenario
create table #tmp (id tinyint, features1 varchar(30), features2 varchar(30))
create table #lookup (id tinyint, feature varchar(30))
insert into #tmp values(5, '2,5,8,','1,3,5,')
insert into #tmp values(6, '1,2,', '7,3,')
insert into #lookup values(1 ,'brown')
insert into #lookup values(2 ,'red')
insert into #lookup values(3 ,'green')
insert into #lookup values(4, 'blue')
insert into #lookup values(5, 'yellow')
insert into #lookup values(6, 'pink')
insert into #lookup values(7 ,'black')
insert into #lookup values(8 ,'orange')

--you could use this to build a proc and pass in the id
declare @string varchar(100)
declare @id varchar(100)
set @id = 6

set @string = (select convert(varchar(10),id) + ' ' + features1 + ' ' + features2
from #tmp
where id = @id)

set @string = replace (@string,'1,','brown, ')
set @string = replace (@string,'2,','red, ')
set @string = replace (@string,'3,','green, ')
set @string = replace (@string,'4,','blue, ')
set @string = replace (@string,'5,','yellow, ')
set @string = replace (@string,'6,','pink, ')
set @string = replace (@string,'7,','black, ')
set @string = replace (@string,'8,','orange, ')

select @string = rtrim(@string)
select @string = substring(@string, 1,len(@string) -1)
--select @string =
select @string

cursors are like hammers - sometimes you have to use them, but watch your thumb!

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-14 : 12:32:05

Thanks very much! This helps however I might be the one that needs to be fired as I just took the data in the format that I recieved in these text files and placed it in the database. The structure of the database is a direct reflection of the data in the files and how it is structured there. If I want to make a change to the database structure what is the best way to take the data from the text files that is in this format:

ID features1 features2
-------------------------------------------
5 2,5,8, 1,3,5,
6 1,2, 7,3,

...and place it in a properly designed structure.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-14 : 15:13:45

It just so happens, that we have some great information on Comma-Separated Values (CSV) in the FAQ, or you can do a Search for CSV. These should help you get started.

------------------------
GENERAL-ly speaking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources