CODEWITHSUNDEEP
sql
Oxy
Oxy

Reputation: 3

SQL replace value from other table if exists

i would like to combine data from two tables. Tables are:

Values                   Translations    
ID   | VALUE             TEXT    |  LANG    | TRANS
--------------           -----------------------------------
20   | 1204              black   |  EN      | black
20   | black             black   |  GER     | schwarz
20   | length            black   |  FR      | noir
20   | dn200             length  |  EN      | length
                         length  |  GER     | länge

Basically i want to check if VALUE exists in Translations table and replace it with selected language. And if VALUE doesn't exist in Translations table leave value of VALUE as is.

Wanted output (when selected language is GER)

VALUE
1204
schwarz
länge
dn200

Wanted output (when selected language is FR)

VALUE
1204
noir
length
dn200

select case when isnull(TRANS, '') = '' then VALUE else TRANS end
from Translations
right join Values
on TEXT = VALUE
where ID = 20

With this clause i get all VALUE values and TRANS values. I don't know how to implement condition for language for german LANG = 'GER'.

Thank you for all your help and directions!

Upvotes: 0

Views: 1717

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would recommend a left join rather than a right join (the semantics are easier to follow: "keep everything in the first table" rather than "keep everything in whatever happens to be the last table".

Then a simple coalesce() suffices:

select coalesce(t.trans, v.value)
from Values v left join
     Translations t
     on v.value = t.text and t.lang = 'GER'
where v.id = 20;

Note that VALUES is a SQL keyword, so it is a bad name for a table. You will probably have to escape the name.

Upvotes: 1

Related Questions