I have 2 tables, illustrated by queries:
CT_Validation
select ValidationID, Message from CT_Validation
ValidationID Message
======================================================
IP_Validator Not a valid IP address
NumbersOnly Invalid number.
SSN Not a valid social security number.
10Digits A 10-digit number is required.
CT_Validation_Lang
select ValidationID, LangID, Message from CT_Validation_Lang
ValidationID LangID Message
======================================================
SSN es Peligro es mi segundo nombre!
How would I build a join so that if LangID = 'es'
I would get back:
ValidationID Message
======================================================
IP_Validator Not a valid IP address
NumbersOnly Invalid number.
SSN Peligro es mi segundo nombre!
10Digits A 10-digit number is required.
...but if LangID
is blank, null, or anything other than 'es', the results would revert to all English:
ValidationID Message
======================================================
IP_Validator Not a valid IP address
NumbersOnly Invalid number.
SSN Not a valid social security number.
10Digits A 10-digit number is required.
Important: The key field is ValidationID
and the important key value in this example is SSN
because that exists in both tables.
5条答案
按热度按时间tct7dpnv1#
I think you want
left join
andcoalesce
This will select the
Message
from theCT_Validation_Lang
if it exists forLangID
es
. Otherwise it will fall back to theMessage
fromCT_Validation
g9icjywg2#
One way with
CASE
g6ll5ycj3#
You can do:
This will choose the message from
CT_Validation_Lang
when the Spanish translation is available. Otherwise, it chooses the original message.h7appiyu4#
Something like: -
jw5wzhpr5#
Use the
ISNULL()
function together with aleft join
.