-1

I don't understand how to retrieve the results from the following tables depending on some prerequisites.

I have 2 tables

Table "project"

| id | category_id | count_view 
| -- | ------------| ---------- 
| 1  | 1           | 100      
| 2  | 2           | 50        
| 3  | 3           | 60          

Table "translations projects"

| id | project_id | name      | lang_code
| -- | -----------| ----------| ---------
| 1  | 1          | some name1| en   
| 2  | 1          | some name2| ru     
| 3  | 2          | some name3| en    
| 4  | 3          | some name4| ru

Result

I want to retrieve results like in the following examples. One condition is that if a translation exists for a preset language then retrieve the name in that language, otherwise retrieve any other language.

For an "en" page

| project_id | name      | lang_code
| -----------| ----------| ---------
| 1          | some name1| en   
| 2          | some name3| en     
| 3          | some name4| ru    

For an "ru" page

| project_id | name      | lang_code
| -----------| ----------| ---------
| 1          | some name2| en   
| 2          | some name3| en     
| 3          | some name4| ru    

Is it possible to achieve what I want?

I tried using case and group with the following statement:

Example for "en" page

SELECT p.id, t.project_name, t.lang_code ,  p.count_view
FROM Projects p 
LEFT JOIN Translation_Projects t 
ON t.project_id = p.id 
and lang_code =
(
CASE
   WHEN t.lang_code = "en" THEN "en"   
   ELSE "ru"
END
)

I am using SQLite.

  • 1
    i.e. you want to obtain the record with the specified language if exist and with another language if specified one not exists, is it? – Akina Aug 27 '18 at 10:29
0
SET @lang := 'en'; -- or 'ru', dependent by the language you need

SELECT p.id, 
       COALESCE(t1.name,t2.name) name, 
       COALESCE(t1.lang_code,t2.lang_code) lang_code
FROM Projects p
LEFT JOIN Translation_Projects t1  ON t1.project_id = p.id 
                                  AND t1.lang_code  = @lang
LEFT JOIN Translation_Projects t2  ON t2.project_id = p.id 
                                  AND t2.lang_code != @lang

PS. You may replace the @lang variable with the literal you need in a query text, of course, having 2 static queries, dynamic SQL or a query with a parameter.

PPS. Assume there will be 3rd language never...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.