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

fzsnzjdm  于 2023-10-15  发布在  其他
关注(0)|答案(5)|浏览(108)

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.

tct7dpnv

tct7dpnv1#

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 LangIDes . Otherwise it will fall back to the Message from CT_Validation

g9icjywg

g9icjywg2#

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'
g6ll5ycj

g6ll5ycj3#

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.

h7appiyu

h7appiyu4#

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;
jw5wzhpr

jw5wzhpr5#

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'

相关问题