Get Multiple Values in One Field from Reference Tbl

[cwsstins]

I've got a table that contains an error code field that can contain multiple error codes (001, 002, 003...) and it is joined to a reference table that contains the values of the codes and their corresponding descriptions:

Tbl1
ErrorCode char(75)

Tbl2
ErrorCode char(3)
Descript char(1000)

In my query, I'm joining Tbl1.ErrorCode = Tbl2.ErrorCode, and I'm using the Tbl2.Descript as the column displayed to the user. When there is only one value in Tbl1.ErrorCode, this works fine. When there is more than one value in Tbl1.ErrorCode, the column in my query is blank. I understand why, but how can I work around this to get multiple descriptions separated by semi-colon or pipe?

 

[bborissov]

1. How's that one error have multiple descriptions?
2. Why ErrorCode in Tbl1 is CHAR(75), but in Tbl2 is CHAR(3)?
3. Why you need Tbl1 anyway?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[cwsstins]

Tbl1 has all the data for the query that will be displayed to the user, including potential error codes. In that table, there is only the code, not the description. But there can be multiple errors in that Code field and they are separated by a comma (001,002).

Tbl2 is a Error table that provides a description for each error. Each error is only 3 characters long but in Tbl1, since there can be multiple errors, it is 75 char.

So each error code has only one description. But in Tbl1, there can be multiple codes, so I'm trying to display multiple descriptions.

For example, here's some sample data from Tbl2 (Error table):
ErrorCode Descript
001 Problem with person file
002 Problem with address file
003 Problem with grade

Here are the columns resulting from my SELECT:
Tbl1.RecordID Tbl1.ErrorCode Tbl2.Descrip
12345 001 Problem with person file
12346 003 Problem with grade
12347 002,003

So for record 12347, I am trying to get my third column to read 'Problem with address file; Problem with grade'

 

[cwsstins]

Those examples formatted a little nicer:

For example, here's some sample data from Tbl2 (Error table):
ErrorCode     Descript
001           Problem with person file
002           Problem with address file
003           Problem with grade

Here are the columns resulting from my SELECT:
Tbl1.RecordID  Tbl1.ErrorCode  Tbl2.Descrip
12345          001             Problem with person file
12346          003             Problem with grade
12347          002,003

 

[markros]

Your problem comes from storing information this way. As you can see, it makes all queries much harder.

The simpler solution will be to redesign the table that stores the error codes this way. Why the error codes are stored as comma-separated values and where this table comes from?

If you can not change the design, you need to first split the comma separated values into individual values (many records) and then you will be able to simply join your tables.

There are many techniques available for splitting values, I suggest to take a look at this:

Integer List Splitting




PluralSight Learning Library