[SOLVED] multi-language query help

[zieloo]

Hi

 

I'm using mysql version 5.0.

 

I'm developing a multilanguage setup that has a very similar database structure to: http://www.codeproject.com/KB/aspnet/LocalizedSamplePart2.aspx#databasedesign  - tables: Item, Culture, Item_Locale just like in the example.

 

I'm having troubles writing a single mysql query that will retrieve ALL the items' Name and Description in localized version if such exists OR in default (any) language in case it's has not yet been translated.

 

The idea is simple - display ALL the items no matter what language they were put in AND, if it's possible, to translate them if applicable row exists in Item_Locale table.

 

Requirements:

- all the items must be shown in the chosen language or in the user-default lang

- the number of languages is variable

- not all the translations will always exist

- there's not one default language (any of them could be 'default' for the user)

- there'll be at least one row for each Item in table Item_Locale

- number of information to translate may be varied. For now it's Name and Desc but I may want to add some more later on.

 

I already tried:

- create a view that was supposed to simplify the query

- using NOT IN() and IN()

- counting number of translations from Item_Locale

- hitting my head against wall 

 

I'd very appreciate any help and suggestions. Thanks.

[fenway]

Perhaps I don't understand where you're stuck.

[zieloo]

Topic solved: http://forums.mysql.com/read.php?10,285558,285558