SQL: JOIN with 'near' match

Question

I need to do a JOIN with a 'near match'. The best way to explain this is with an example:

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)

The SPROC would look like this:

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)

See http://sqlfiddle.com/#!6/ac24d/24 (thanks to Jake!)

Here is the challenge: When the SPROC parameter @LanguageCode is an exact match, all is well.

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').

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').

Answer 1

Try left(@LanguageCode, 2) + '%'

http://sqlfiddle.com/#!6/ac24d/26

About second part - you have to query table two times anyway (you can do it in one statement, but if will be like two statements in one). You also can insert data into temporary (or variable) table, check if there's no rows and then make another query

I've made a query with table function

http://sqlfiddle.com/#!6/b7be3/5

So you can write

DECLARE @LanguageCode varchar(12) = 'es'

if not exists (select * from sf_test(@LanguageCode))
    select * from sf_test('en')
else 
    select * from sf_test(@LanguageCode)

you also can write

declare @temp table
(
    Vin int,
    Make nvarchar(50),
    ColorCode nvarchar(10)
)

insert into @temp
select * from sf_test(@LanguageCode)

if not exists (select * from @temp)
    select * from sf_test('en')
else 
    select * from @temp

Answer 2

As @Roman Pekar has said in his comment, this can indeed be done, including your additional request about falling back to en, in one statement with the help of a ranking function. Here's how you could go about it:

WITH FilteredAndRanked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (
      PARTITION BY ColorID
      ORDER BY CASE LanguageCode
        WHEN @LanguageCode          THEN 1
        WHEN LEFT(@LanguageCode, 2) THEN 2
        WHEN 'en'                   THEN 3
      END
    )
  FROM ColorName
  WHERE LanguageCode IN (
    @LanguageCode,
    LEFT(@LanguageCode, 2),
    'en'
  )
)
SELECT
  ...
FROM       Car               A
INNER JOIN Color             B ON (A.ColorID = B.ColorID)
LEFT  JOIN FilteredAndRanked C ON (B.ColorID = C.ColorID AND C.rnk = 1)

;

That is, the ColorName table is filtered and ranked before being used in the query, and then only the rows with the rankings of 1 are joined:

1. The filter for ColorName includes only rows with LanguageCode values of @LanguageCode, LEFT(@LanguageCode, 2) and 'en'.

2. The ranking values are assigned based on which language code each row contains: rows with LEFT(@LanguageCode, 2) are ranked after those with @LanguageCode but before the 'en' ones.