複数のキー項目で合致の無い場合、キー項目を減らして合致する行を探したい

Question

前提・実現したいこと

SQL Server 2014 についての質問です。
実データの入ったテーブル(以下コードでいうDataTable)と、それらに関わる設定値的な内容が入ったテーブル(以下コードで言うSettings)の2つを用意し、
・それぞれのcode1とcode2とcode3が合致する行を
・上記で合致するものがなければ、code1とcode2が合致する行を
・上記で合致するものがなければ、code1が合致する行を
取得(結合)したいと思っています。

発生している問題

以下のようなクエリなどを書いてみましたが、データ2・データ3・データ4は複数のSettings行が合致してしまい、希望通りに行きませんでした。
何かいい方法はありませんでしょうか。

該当のソースコード

-- DataTableに対応する設定内容が定義されているテーブル
CREATE TABLE Settings
    ([code1] varchar(2) NOT NULL, [code2] varchar(3), [code3] varchar(4), [status1] varchar(30))
;
INSERT INTO Settings
    ([code1], [code2], [code3], [status1])
VALUES
    ('01', '', '', '設定01'),      -- 設定1
    ('01', '001', '', '設定001'),    -- 設定2
    ('01', '001', '0002', '設定0002'), -- 設定3
    ('01', '001', '0003', '設定0003')  -- 設定4
;

-- 3つのキーコードを持つテーブル
-- code1・code2は必ず値が入る想定。
CREATE TABLE DataTable
    ([code1] varchar(2) NOT NULL, [code2] varchar(3) NOT NULL, [code3] varchar(4))
;
INSERT INTO DataTable
    ([code1], [code2], [code3])
VALUES
    ('01', '111', '1111'),  -- データ1
    ('01', '001', '2222'),  -- データ2
    ('01', '001', '3333'),  -- データ3
    ('01', '001', '0003')   -- データ4
--   ：
--  (略)
--   ：
;

期待する結果は以下です。
・データ1 → 設定1が紐付く
・データ2 → 設定2が紐付く
・データ3 → 設定2が紐付く
・データ4 → 設定4が紐付く

試してみたクエリ1

SELECT dt.code1,dt.code2,dt.code3,st.status1 FROM DataTable AS dt
LEFT JOIN Settings AS st
ON
     dt.code1=st.code1
 and (dt.code2=st.code2 OR st.code2='')
 and (dt.code3=st.code3 OR st.code3='')
;

試してみたクエリ2

SELECT dt.code1,dt.code2,dt.code3,st.status1 FROM DataTable AS dt
LEFT JOIN Settings AS st
ON
     dt.code1=st.code1
 and st.code2 = case when dt.code2=st.code2 then dt.code2 else '' end
 and st.code3 = case when dt.code3=st.code3 then dt.code3 else '' end
;
 

実際は件数の多いデータになりますので、できるだけ軽く処理できればと思っているのですが…

よろしくお願いします。

Answer 1

1行だけ欲しいなら

SELECT TOP(1) * FROM DataTable AS dt
LEFT JOIN Settings AS st
ON
  dt.code1 = st.code1
ORDER BY
  CASE WHEN dt.code2 = st.code2 THEN -2 END
  + CASE WHEN dt.code3 = st.code3 THEN -3 END

速度が欲しいならストアドでゴリゴリかな？

コメントを受けての追記

SELECT dt.*,
(
  SELECT TOP(1) st.status1
  FROM Settings AS st
  WHERE dt.code1 = st.code1
  ORDER BY
  CASE WHEN dt.code2 = st.code2 THEN -2 END
  + CASE WHEN dt.code3 = st.code3 THEN -3 END
) AS Setting
FROM DataTable AS dt

Comments

[退会済みユーザー]

早速のご回答ありがとうございます！

DataTableの1行ごとにSettingsの1行を紐付けたいのですが、SELECT文1文で実現は難しいのでしょうか。

[hihijiji]

追記しました。
こんなのでしょうか？

[退会済みユーザー]

追記ありがとうございます！

実行してみたのですが、結果が全部「設定1」が紐付いてしまうようです。
以下のような結果を期待しています。

データ1 → 設定1が紐付く。
データ2 → 設定2が紐付く。
データ3 → 設定2が紐付く。
データ4 → 設定4が紐付く。

記載不足で申し訳ないです。(本文にも追記しました。)
またお知恵を拝借できれば幸いです。
よろしくお願いします。

Answer 2

詳しい方にご助言いただき、なんとかやりたいことを実現することができました。
随分と長くなってしまいますが…＾＾；

-- 1つめの条件に合致するものを取得
select '条件1' as '合致条件', dt.code1 as code1, dt.code2 as code2, dt.code3 as code3, st.status1
from DataTable as dt
    inner join Settings as st
    on
        dt.code1 = st.code1
    and dt.code2 = st.code2
    and dt.code3 = st.code3
-- 2つめの条件に合致するものを取得・結合
union all
select '条件2' as '合致条件', dt.code1 as code1, dt.code2 as code2, dt.code3 as code3, st.status1
from DataTable as dt
    inner join Settings as st
    on
        dt.code1 = st.code1
    and dt.code2 = st.code2
    and st.code2 <> ''
    and st.code3 = ''
where
    -- 1つめの条件に合致するものを除外
    not exists(
        select 'X' from DataTable as dt_ex
        inner join Settings as st_ex
        on
            dt.code1 = dt_ex.code1
        and dt.code2 = dt_ex.code2
        and dt.code3 = dt_ex.code3
        and dt_ex.code1 = st_ex.code1
        and dt_ex.code2 = st_ex.code2
        and dt_ex.code3 = st_ex.code3
    )
-- 3つめの条件に合致するものを取得・結合
union all
select '条件3' as '合致条件', dt.code1 as code1, dt.code2 as code2, dt.code3 as code3, st.status1
from DataTable as dt
    inner join Settings as st
    on
        dt.code1 = st.code1
    and st.code2 = ''
    and st.code3 = ''
where
    -- 1つめの条件に合致するものを除外
    not exists(
        select 'X' from DataTable as dt_ex
        inner join Settings as st_ex
        on
            dt.code1 = dt_ex.code1
        and dt.code2 = dt_ex.code2
        and dt.code3 = dt_ex.code3
        and dt_ex.code1 = st_ex.code1
        and dt_ex.code2 = st_ex.code2
        and dt_ex.code3 = st_ex.code3
    )
    and
    -- 2つめの条件に合致するものを除外
    not exists(
        select 'X' from DataTable as dt_ex
        inner join Settings as st_ex
        on
            dt.code1 = dt_ex.code1
        and dt.code2 = dt_ex.code2
        and dt.code3 = dt_ex.code3
        and dt_ex.code1 = st_ex.code1
        and dt_ex.code2 = st_ex.code2
        and st_ex.code2 <> ''
        and st_ex.code3 = ''
    )
;

ご回答くださったhihijiji様、ならびに閲覧いただきました方々、お忙しい中お時間をいただきありがとうございました。
お騒がせ致しました。

別の方法などがあれば、ご教示いただけますと幸いです。
何かありましたら、その時はまた何卒よろしくお願い致します。