Geek Answers Handbook

SQL Server: how to return 1 row by default if the condition is not met

I have a message table that contains a message id field, a language id field and a text field. The application should display a message based on the identifier and language, which together form a unique key. All messages exist for the EN language, but not all have been translated into other languages. Therefore, for the English language, one album will always be selected. But if the user is French, and the application should display message No. 17, and it does not yet exist for the French language, I want to return message No. 17 to EN . I would like to execute this in one SELECT query, preferably without IF expressions.

EDIT: based on the answers provided - it is necessary to clarify that each message is translated into 10 languages, and maybe more. there must always be exactly one line returned based on the message id and lang id. but if this line does not exist, you must return the English message.

Final code:
declare @msgid int=2, @langid varchar(2)='fr' SELECT isnull(xx.msg, en.msg) msgtext FROM appmessages en LEFT JOIN appmessages xx ON en.msgid = xx.msgid and xx.langid=@langid WHERE en.langid = 'en' and en.msgid=@msgid

+6
source share
5 answers

You can use LEFT JOIN and ISNULL to achieve this.

 SELECT ISNULL(fr.message, en.message) AS message FROM message_table en LEFT JOIN message_table fr ON fr.message_id = en.message_id AND fr.language = 'French' WHERE en.message_id = 17 AND en.language = 'English'
+7
source

A simple approach simply uses order by to prioritize:

 select top 1 mt.* from message_table mt where mt.message_id = 17 and mt.language in ('French', 'English') order by (case when mt.language = 'French' then 1 else 2 end);

For a single message, performance is optimized with an index on message_table(message_id, language) . order by on two lines is trivial, so it should be very fast.

This approach also has a nice characteristic that it works, even if some messages do not have translations into English. It is also quite simple to add additional languages, and the impact on processing time should be minimal.

For a few posts, you can do something similar with window functions, but that will be another question.

+5
source 
 DECLARE @UserLanguageID char(2)='fr', @DefaultLanguageID char(2)='en' SELECT COALESCE(uMsg.KeyField, dMsg.KeyField) AS KeyField, COALESCE(uMsg.LanguageID, dMsg.LanguageID) AS LanguageID, COALESCE(uMsg.Text, dMsg.Text) AS Text FROM Messages dMsg LEFT JOIN Messages uMsg ON uMsg.KeyField=dMsg.KeyField WHERE ( uMsg.KeyField=@KeyField OR dMsg.KeyField=@KeyField ) AND uMsg.LanguageID=@UserLanguageID AND dMsg.KeyField=@DefaultLanguageID
+3
source 
 DECLARE @RequestedLanguageId NCHAR(2) DECLARE @RequestedMessageId INT SET @RequestedLanguageId = 'FR' SET @RequestedMessageId = 18 SELECT ISNULL(xx.message_text, en.message_text) AS MessageText FROM message_table en LEFT JOIN message_table xx ON xx.message_id = en.message_id AND xx.language_id = @RequestedLanguageId WHERE (en.language_id = 'EN') AND (en.message_id = @RequestedMessageId)

The DECLARE and SETs variables let you check this out.

0
source

declare @msgid int=2, @langid varchar(2)='fr' SELECT isnull(xx.msg, en.msg) msgtext FROM appmessages en LEFT JOIN appmessages xx ON en.msgid = xx.msgid and xx.langid=@langid WHERE en.langid = 'en' and en.msgid=@msgid

0
source

More articles:


All Articles