SELECT文中でマスタデータを参照し、表示結果を分けたい

Question

お世話になります。

どう説明していいかわからないので例を下記に記載します。

下記のようなマスタデータがあり
[tbl_m_ken]

No	mst_ken
0	hokkaido
1	aomori
2	akita
3	iwate
4	yamagata
5	miyagi
6	fukushima

下記のようなトランデータに自由に入力させます。
[tbl_t_ken]

No	trn_ken
0	京都
1	大阪
2	愛知
3	akita
4	高知
5	熊本
6	鹿児島

DBでデータを抽出した際に、

SELECT
    (SELECT
       CONCAT('ローマ字の', mst_ken) 
     FROM
       tbl_m_ken
     WHERE
       tbl_m_ken.mst_ken = tbl_t_ken.trn_ken AND
       tbl_m_ken.mst_ken IS NOT NULL AND
       tbl_m_ken.trn_ken <> '' LIMIT 1)
     AS trn_ken
FROM
    tbl_t_ken
WHERE
    tbl_t_ken.No = 3

上記だったら「ローマ字のakita」と表示されると思うのですが、
これにもうひと加えして、[tbl_t_ken.No = 3]で[akita]以外が指定された場合(tbl_t_ken.No = 4)や(tbl_t_ken.No = 2)は
ただ「高知」や「愛知」とそのまま出力したいと思っています。

CASE WHENを使用したら良いのかとも思いましたが、
CASE WHENの中でマスタのデータを参照するというのがよくわからず困っています。
御助力いただけないでしょうか。

Answer 1

ちょっとありえないロジックですが、無理やりやればこんな感じ？

• 元データ

create table tbl_m_ken(No int,mst_ken varchar(30));
insert into tbl_m_ken values
(0 ,'hokkaido'),
(1 ,'aomori'),
(2 ,'akita'),
(3 ,'iwate'),
(4 ,'yamagata'),
(5 ,'miyagi'),
(6 ,'fukushima');

create table tbl_t_ken(No int,trn_ken varchar(30));
insert into tbl_t_ken values
(0 ,'京都'),
(1 ,'大阪'),
(2 ,'愛知'),
(3 ,'akita'),
(4 ,'高知'),
(5 ,'熊本'),
(6 ,'鹿児島');

• No=3のときの処理

SELECT case t1.mst_ken when t2.trn_ken then concat('ローマ字の', t1.mst_ken) 
else t2.trn_ken end as x
FROM tbl_m_ken as t1
INNER JOIN  tbl_t_ken as t2 on t2.No=3
WHERE t1.mst_ken=t2.trn_ken or t1.No=t2.No
order by t1.mst_ken=t2.trn_ken desc
limit 1

• No=2のときの処理

SELECT case t1.mst_ken when t2.trn_ken then concat('ローマ字の', t1.mst_ken) 
else t2.trn_ken end as x
FROM tbl_m_ken as t1
INNER JOIN  tbl_t_ken as t2 on t2.No=2
WHERE t1.mst_ken=t2.trn_ken or t1.No=t2.No
order by t1.mst_ken=t2.trn_ken desc
limit 1

Comments

[asus2]

解答ありがとうございます、お礼が遅くなり申し訳ありません。

Answer 2

テーブルAのある項目をテーブルBから探す、という処理は基本的にJOINで行います。
今回の場合はマスターテーブルにないケースも探したいので、 LEFT JOIN を使うと良いです。

SELECT
  IFNULL(tbl_m_ken.mst_ken, tbl_t_ken.trn_ken)
FROM
  tbl_t_ken
LEFT JOIN
  tbl_m_ken 
ON
  tbl_t_ken.trn_ken = tbl_m_ken.mst_ken
WHERE
    tbl_t_ken.No = 3

IFNULL は自分で調べてみてください。

Comments

[mather]

'ローマ字の' の部分を入れ忘れました。 IFNULLがわかれば、case 文でできると思います。

[asus2]

解答ありがとうございます。
返信が遅くなり申し訳ありません。