Building SQL query: Using values in table to dictate columns?

Question

I THINK I remember doing this before and I am drawing a huge blank on how to do it or even the correct terminology to adequately search for it.

I am trying to build a query that is gets a value from a table called tblTranslate and which column I need to select is based off an field in another table. tblTranslate is filled with words translated into different languages thus when in my query I ask to see a specific row in SpanishI want the name displayed as Spanish , but it is only that one.

For example:

In my tblTranslate I have the following
instance_id - name_eng - name_span
1 - Dog - Perro
2 - Frog - Rana
3 - Cat - Gato
4 - Cow - Vaca

in my tblLanguages I shave 
language_id - trans_col
1 - name_eng
2 - name_span

I have a table like the following:
instance_id - language_id
1 - 1
2 - 1
3 - 1 
3 - 2
4 - 1

And I want my final output to be:
1 - Dog
2 - Frog
3 - Cat
3 - Gato
4 - Cow

So based on the language_id in my select it gets the trans_col associated with that language  then goes and uses the appropriate column form my table with the list of translations. This is an overtly simplified example. I have 20000+ records and 12 different languages to look for to display. 

Any help with the query or where to find/what exact to search for would be helpful.

Ratchetr · Accepted Answer

Your table design for tblTranslate is wrong. Is it too late to change it?

You want to design your table so that you can specify which set of *rows* to return in a query. You do *not* want to try to specify which set of columns based on your search criteria. It's hard to do that in SQL. (There are ways, such as dynamic SQL. Or you could use the union approach. But they will be hard.)

Change tblTranslate  to look like this:
instance_id - language_id name
1 - 1 - Dog
1 - 2 - Perro
2 - 1 -  Frog 
2 - 2 - Rana
3 - 1 - Cat 
3 - 2 - Gato
4 - 1 - Cow 
4 - 2 - Vaca

The language_id column is a foreign key to the language_id column in tblLanguages.

Now your query becomes very easy:
SELECT instance_id, name
FROM table t
JOIN tblTranslate tx ON t.instance_id = tx.instance_id  AND t.language_id = tx.language_id;

Or if you prefer this style:
SELECT instance_id, name
FROM table t, tblTranslate tx
WHERE t.instance_id = tx.instance_id  
 AND t.language_id = tx.language_id;

Much simpler, no?

Serge M · Answer

select instance_id, 
case language_id when 1 then name_eng when 2 then name_span end name
from I_have_a_table_like_the_following i join tblTranslate t on t.instance_id =i.instance_id

Zorro · Answer

You would need to use a union statement, and then a sort by statement.

Trending News

Building SQL query: Using values in table to dictate columns?

3 Answers