How to choose 1 record from 2 tables based on record existence?

Question

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.

Thanks in advance for any advice.

Possible duplicate of [Can I use CASE statement in a JOIN condition?](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition) — Vj-, Oct 18 '17 at 01:54

score 2 · Accepted Answer · answered Oct 18 '17 at 01:57

2

I think you want left join and coalesce

select cv.ValidationID, coalesce(cvl.Message, cv.Message) 
from CT_Validation cv
left join CT_Validation_Lang cvl on cv.ValidationID = cvl.ValidationID
     and cvl.LangID = 'es'

This will select the Message from the CT_Validation_Lang if it exists for LangID es. Otherwise it will fall back to the Message from CT_Validation

answered Oct 18 '17 at 01:57

FuzzyTree

32,014
3
54
85

Works great! Thank you FuzzyTree. – HerrimanCoder Oct 18 '17 at 14:47

score 1 · Answer 2 · answered Oct 18 '17 at 02:01

1

One way with CASE

SELECT
  ctv.ValidationID,
  CASE WHEN ctvl.LangID = 'es' THEN ctvl.message ELSE ctv.message END AS Message
FROM CT_Validation ctv
LEFT JOIN CT_Validation_Lang ctvl ON ctv.ValidationId = ctvl.ValidationId
  AND ctvl.LangID = 'es'

answered Oct 18 '17 at 02:01

SQLChao

7,709
1
17
32

score 0 · Answer 3 · answered Oct 18 '17 at 01:57

You can do:

select v.ValidationID, coalesce(vl.message, v.message) as message
from CT_Validation v left join
     CT_Validation_Lang vl
     ON vl.ValidationID = v.ValidationID and vl.LangID = 'es';

This will choose the message from CT_Validation_Lang when the Spanish translation is available. Otherwise, it chooses the original message.

score 0 · Answer 4 · answered Oct 18 '17 at 02:01

Something like: -

select 
  ctv.ValidationID, 
  coalesce(
          (select 
             ctvl.Message 
           from 
             CT_Validation_Lang ctvl 
           where 
             ctvl.ValidationID = ctv.ValidationID and 
             LangID = 'es'
           ), 
           ctv.Message, ' ') as Message 
  from 
    CT_Validation ctv;

score 0 · Answer 5 · answered Oct 18 '17 at 02:09

0

Use the ISNULL() function together with a left join.

select a.ValidationID, ISNULL(b.message, a.message) 
from CT_Validation a
left join CT_Validation_Lang b on a.ValidationID = b.ValidationID and b.LangID = 'es'

answered Oct 18 '17 at 02:09

Andy Refuerzo

3,312
1
31
38

How to choose 1 record from 2 tables based on record existence?

5 Answers5