A non-relational prob

Hi

Here's my problem:
In our db, we have a lot of items whose name (or text string) is identified by a text id. Each item has exactly one or none text id. Now, the texts identified by this text id might be available in different languages and in different countries (this meaning that the canadian/french text is not nessessarily the same as the french/french text). For each text id, there is always a default text, to be displayed when a language/country form for this text id lacks.

This led to the following table txt:

text_id country language t

1         any       --         house
1         fr        fr         maison
2         any       --         boat
3         any       --         phone
3         ch        de         telefon
3         ch        fr         telephone
3         fr        fr         tph

This means: for text_id 1 ('house') we have a french translation ('maison')

Whenever a french speaking person wishes to see text_id 1, it sees maison while all others see house (even german, polish, dutch, what ever speaking persons)

for text id 2, evryone sees boat.

for text id 3, evryone sees phone except swiss (ch) and french (fr) people. german (de) speaking persons from switzerland are going to see telefon, while french speaking persons from switzerland are going to see telephone, while french speaking persons from france (fr) are going to see tph.

Now, the question: how would I desing an sql select statement, that, given the language and the country of a user, returns the correct text in a _single_ line for a text id?

TIA Rene

for text id 3, evryone Received on Mon Mar 19 2001 - 19:04:26 CST