CODEWITHSUNDEEP
mysqlselectmariadb
Francesc Arolas
Francesc Arolas

Reputation: 355

How to SELECT an specific value or any other in a 1-N tables relation

I would like to find a query to perform the following results, I've tried a lot of differents methods but I can't really find the proper way to do it, I would appreciate if someone can help me on this.

Keep in mind that I can have a lot of different languages and I always want to prioritize one.

Having the following tables:

item

id info
1 Etc
2 Etc

item_translation

item_id lang_id value
1 1 English Translation for item 1
1 2 French translation for item 1
2 2 French translation for item 2

I would like to select all items and only one item_translation for each item prioritizing a language. For example if I would like to select all items prioritizing language 1 it should return the following:

id info value lang_id
1 Etc English Translation for item 1 1
2 Etc French translation for item 2 2

And if I would like to prioritize language 2:

id info value lang_id
1 Etc French translation for item 1 2
2 Etc French translation for item 2 2

Upvotes: 0

Views: 63

Answers (1)

The Impaler
The Impaler

Reputation: 48769

To prioritize lang_id #1 you can do:

select
  i.id,
  i.info,
  x.value,
  x.lang_id
from item i
left join (
  select *,
    row_number() over(partition by item_id 
                      order by case when lang_id = 
      1 -- lang_id to prioritize
      then 1 else 2 end) as rn
  from item_translation
) x on x.item_id = i.id and x.rn = 1

Result:

 id  info  value                           lang_id 
 --- ----- ------------------------------- ------- 
 1   Etc   English Translation for item 1  1       
 2   Etc   French Translation for item 2   2

See running example at DB Fiddle.

It will show:

  • When multiple languages are available it will prioritize the preferred language.
  • When the preferred language is not available it will select a random other.
  • When no translation is available it will show nulls.

Upvotes: 2

Related Questions