Unique string table in SQL and replacing index values with string values during query

Question

I'm working on an old SQL Server database that has several tables that look like the following:

|-------------|-----------|-------|------------|------------|-----|
| MachineName | AlarmName | Event | AlarmValue | SampleTime | ... |
|-------------|-----------|-------|------------|------------|-----|
| 3           | 180       | 8     | 6.780      | 2014-02-24 |     |
| 9           | 67        | 8     | 1.45       | 2014-02-25 |     |
| ...         |           |       |            |            |     |
|-------------|-----------|-------|------------|------------|-----|

There is a separate table in the database that only contains unique strings, as well as the index for each unique string. The unique string table looks like this:

|----------|--------------------------------|
| Id       | String                         |
|----------|--------------------------------|
| 3        | MyMachine                      |
| ...      |                                |
| 8        | High CPU Usage                 |
| ...      |                                |
| 67       | 404 Error                      |
| ...      |                                |
|----------|--------------------------------|

Thus, when we want to get something out of the database, we get the respective rows out, then lookup each missing string based on the index value.

What I'm hoping to do is to replace all of the string indexes with the actual values in a single query without having to do post-processing on the query result.

However, I can't figure out how to do this in a single query. Do I need to use multiple JOINs? I've only been able to figure out how to replace a single value by doing something like -

SELECT UniqueString.String AS "MachineName" FROM UniqueString 
JOIN Alarm ON Alarm.MachineName = UniqueString.Id

Any help would be much appreciated!

@albe - Yes, all values that aren't numerical or DateTime are stored as a unique string. — Stephen
– Stephen, Commented Mar 26, 2014 at 20:04

bitfiddler · Accepted Answer · 2014-03-26 00:50:48Z

0

Yes, you can do multiple joins to the UniqueStrings table, but change the order to start with the table you are reporting on and use unique aliases for the joined table. Something like:

SELECT MN.String AS 'MachineName', AN.String as 'AlarmName'  FROM Alarm A 
  JOIN UniqueString MN ON A.MachineName = MN.Id
  JOIN UniqueString AN ON A.AlarmName = AN.Id

etc for any other columns

answered Mar 26, 2014 at 0:50

bitfiddler

2,1151 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bitfiddler Over a year ago

Cool, glad it helped. I don't use JDBC, but a quick search showed this article on SO (and some others) that might help stackoverflow.com/questions/8440429/… It's likely in your calling params

Collectives™ on Stack Overflow

Unique string table in SQL and replacing index values with string values during query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related