StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL: JOIN with 'near' match
text
Body
copied!<p>I need to do a JOIN with a 'near match'. The best way to explain this is with an example:</p> <pre><code>CREATE TABLE Car ( Vin int, Make nvarchar(50), ColorID int, ) CREATE TABLE Color ( ColorID int, ColorCode nvarchar(10) ) CREATE TABLE ColorName ( ColorID int, Languagecode varchar(12), ColorName nvarchar(50) ) INSERT INTO Color Values (1, 'RED CODE') INSERT INTO Color Values (2, 'GREEN CODE') INSERT INTO Color Values (3, 'BLUE CODE') INSERT INTO ColorName Values (1, 'en', 'Red') INSERT INTO ColorName Values (1, 'en-US', 'Red, my friend') INSERT INTO ColorName Values (1, 'en-GB', 'Red, my dear') INSERT INTO ColorName Values (1, 'en-AU', 'Red, mate') INSERT INTO ColorName Values (1, 'fr', 'Rouge') INSERT INTO ColorName Values (1, 'fr-BE', 'Rouge, mon ami') INSERT INTO ColorName Values (1, 'fr-CA', 'Rouge, mon chum') INSERT INTO Car Values (123, 'Honda', 1) </code></pre> <p>The SPROC would look like this:</p> <pre><code>DECLARE @LanguageCode varchar(12) = 'en-US' SELECT * FROM Car A JOIN Color B ON (A.ColorID = B.ColorID) LEFT JOIN ColorName C ON (B.ColorID = C.ColorID AND C.LanguageCode = @LanguageCode) </code></pre> <p>See <a href="http://sqlfiddle.com/#!6/ac24d/24" rel="nofollow">http://sqlfiddle.com/#!6/ac24d/24</a> (thanks to Jake!)</p> <p>Here is the challenge: When the SPROC parameter @LanguageCode is an exact match, all is well.</p> <p>I would like for it to also work for partial matches; more specifically: say for example that @LanguageCode would be 'en-NZ' then I would like the SPROC to return the value for language code 'en' (since there is no value for 'en-NZ').</p> <p>As an extra challenge: if there is no match at all I would like to return the 'en' value; for example if @LanguageCode would be 'es' then the SPROC would return the 'en' value (since there is no value for 'es').</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload