CODEWITHSUNDEEP
sqlsql-serverdatabasejoin
Martin Zikmund
Martin Zikmund

Reputation: 39092

Conditional SQL query column mapping

I have two database tables - Categories and LocalizedCategories. The Categories table has Name and Description columns for all categories in the default language. The LocalizedCategories table also has these columns which contain translations of these columns into other languages (every content language has its id, so primary key of the table consists of ContentLanguageId and CategoryId). I would like now to perform a SELECT query to get all categories so that the textual data in the results are localized in case a translation is available for the given category and content language. If the translation is not available, or the content language is the default one, I would like to fall back to the defaults in the Categories table.

How would such a query look like? Is there a better way to separate the tables so that querying and working with the data is simpler?

Upvotes: 0

Views: 703

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

Your table design seems fine. To get data only when it exists, is done with an outer join. Then use COALESCE to get one value or the other.

select 
  coalesce(lc.name, c.name),
  coalesce(lc.description, c.description)
from categories c
left outer join localizedcategories lc 
  on lc.categoryid = c.categoryid and lc.contentlanguageid = 'EN';

Upvotes: 2

A ツ
A ツ

Reputation: 1267

simple, just do a left join :

select Description = coalesce(lc.Description, c.Description )
     , Name        = coalesce(lc.Name, c.Name)
     , Lang        = coalesce(lc.Lang, DefaultContentLanguageId )
from Categories c 
left join LocalizedCategories lc on c.CategoryId=lc.CategoryId and lc.ContentLanguageId = ?

Upvotes: 2

Yuri Blanc
Yuri Blanc

Reputation: 645

The database schema is ok. You use a category default and a translated one with a language table. So you have 1->lang n->cat transaltions to ->n cats.

You sql should query the db in order to:

SELECT * FROM Categories INNER JOIN LocalizedCategories USING (Category_id) WHERE ContentLanguageId=?

This will return 0 rows, if the given language does not have a translation to the Category_id. In your server language you can accomplish these and implement the logic behind that, if the given query does not return any data, query the default table and fill the displayed data with them.

Upvotes: 1

Related Questions