54942


以下のような二つのテーブルが存在します。

[code]

    CREATE TABLE `tableA` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `uuid` varchar(255) ,
      `geo_latitude` decimal(10,7) DEFAULT NULL COMMENT 'スポット緯度',
      `geo_longitude` decimal(10,7) DEFAULT NULL COMMENT 'スポット経度',
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (id)
    ) DEFAULT CHARSET = utf8;
    
[/code]

[code]

    CREATE TABLE `tableB` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `tableA_id` bigint(20) DEFAULT NULL ,
      `language_code_id` int(11) DEFAULT NULL,
      `value` text NOT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (id),
      FOREIGN KEY (tableA_id) REFERENCES tableA (id)
    ) DEFAULT CHARSET = utf8;
    
[/code]

tableAとtableBの関係は、tableAが基本となる情報を保持しているのですが、このtabelAの多言語の情報をtableBが保持するようになっています。また、tabelAが保持するtableBの多言語情報は基本的に不定なのですが、日本語(languagecode=1)は必ず持つような作りになっています。

このような状況の中で、特定の緯度経度から近い物を検索して、指定された言語コードのvalueを取得,  
もし指定の言語がなければ日本語を取得するというsqlを実行したいです。  
現状緯度経度部分のsqlはかけているのですが、”指定された言語コードのvalueを取得,  
もし指定の言語がなければ日本語を取得”という部分のクエリがかけていないです。

ご教授いただけると幸いです。  
ちなみに環境はMySQLです。  
\--現状のクエリ--

[code]

    SELECT 
    tableA.id, tableA.uuid, geo_latitude AS latitude, geo_longitude AS longitude, ( 6371000 * acos( cos(radians(35.730)) * cos(radians(geo_latitude)) * cos(radians(geo_longitude) - radians(139.831)) + sin(radians(35.730)) * sin(radians(geo_latitude)) ) ) AS distance, 
    `tableB`.`language_code_id`, `tableB`.`value`
    FROM tableA
    INNER JOIN `tableB` ON tableA.id = `tableB`.`tableA_id`
    HAVING distance <= 100.0 
    ORDER BY distance;
    
[/code]

\--tableAテストデータ---

[code]

    INSERT INTO `tableA` (`id`, `uuid`, `geo_latitude`, `geo_longitude`, `created_at`, `updated_at`)
    VALUES
    (1,'f4975f27-e07a-4389-b615-a566fa66ba08',35.730,139.830,'2019-04-17 11:47:49','2019-04-17 11:47:49'),
    (2,'f4975f27-e07a-4389-b615-a566fa66ba09',35.7301,139.8301,'2019-04-17 11:47:49','2019-04-17 11:47:49'),
    (3,'f4975f27-e07a-4389-b615-a566fa66ba10',35.7302,139.8302,'2019-04-17 11:47:49','2019-04-17 11:47:49'),
    (4,'f4975f27-e07a-4389-b615-a566fa66ba11',35.7303,139.8303,'2019-04-17 11:47:49','2019-04-17 11:47:49'),
    (5,'f4975f27-e07a-4389-b615-a566fa66ba11',35.734,139.834,'2019-04-17 11:47:49','2019-04-17 11:47:49');
    
[/code]

\--tableBテストデータ--

[code]

    INSERT INTO `tableB` (`id`, `tableA_id`, `language_code_id`, `value`, `created_at`, `updated_at`)
    VALUES
        (1,1,1,'テストデータ1_日本語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (2,1,2,'テストデータ1_英語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (3,1,3,'テストデータ1_中国語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (4,2,1,'テストデータ2_日本語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (5,2,2,'テストデータ2_英語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (6,3,1,'テストデータ3_日本語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (7,4,1,'テストデータ4_日本語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (8,4,2,'テストデータ4_英語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (9,4,3,'テストデータ4_中国語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (10,4,4,'テストデータ4_韓国語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (11,5,1,'テストデータ5_日本語','2019-04-17 11:49:00','2019-04-17 11:49:00'),
        (12,5,4,'テストデータ5_韓国語','2019-04-17 11:49:00','2019-04-17 11:49:00');
    
[/code]

\-- language_code_id対応表 ---  
id 言語  
1 日本語  
2 英語  
3 中国語  
4 韓国語  
5 タイ語

上記のテストデータの場合、やりたいことは  
条件は35.730,139.830の地点より100m以内にあるもの  
取得したいデータは  
tableAの各カラムと、検索地点からの取得データまでの距離、  
tableBのvalueなのですが、中国語のvalueが欲しいのだけれど、中国語がない場合は日本語が欲しい。  
という事になります。

この場合取得したいデータ(tableAの各カラムは省略しています)は  
tableA.id, tableB.language_code_id, tableB.value  
1, 3, 'テストデータ1_中国語'  
2, 1, 'テストデータ2_日本語'  
3, 1, 'テストデータ3_日本語'  
4, 3, 'テストデータ4_中国語'  
になります。


CC BY-SA 4.0
2019-05-13T03:26:13.917
54923
2019-05-14T04:11:31.317
2019-05-14T02:36:06.957
33037
33037
question
mysql sql
joinしたテーブルに対して、特定のカラムの検索条件がない場合は、同じカラムへの違う条件で検索したい


改善できるクエリだと思いますが、一応やりたい事は機能しているようなので、回答します。

[code]

    SELECT 
      tableA.id,
      tableA.uuid,
      tableA.geo_latitude AS latitude,
      geo_longitude AS longitude, 
      ( 6371000 * acos( cos(radians(35.730)) * cos(radians(geo_latitude)) * cos(radians(geo_longitude) - radians(139.831)) + sin(radians(35.730)) * sin(radians(geo_latitude)) ) ) AS distance,
      IFNULL(tableB_selected_by_language_code.language_code_id, 1) as selected_language_code_id,
      (SELECT value from tableB where tableA_id = tableA.id and language_code_id = selected_language_code_id LIMIT 1)
    FROM 
      tableA
    LEFT JOIN 
      (SELECt tableA_id, language_code_id from tableB where language_code_id = 3) as tableB_selected_by_language_code ON tableA.id = tableB_selected_by_language_code.tableA_id
    HAVING 
      distance <= 100.0;
    
[/code]

結果

[code]

    +----+--------------------------------------+------------+-------------+-------------------+---------------------------+-----------------------------------------------------------------------------------------------------------------+
    | id | uuid                                 | latitude   | longitude   | distance          | selected_language_code_id | (SELECT value from tableB where tableA_id = tableA.id and language_code_id = selected_language_code_id LIMIT 1) |
    +----+--------------------------------------+------------+-------------+-------------------+---------------------------+-----------------------------------------------------------------------------------------------------------------+
    |  1 | f4975f27-e07a-4389-b615-a566fa66ba08 | 35.7300000 | 139.8300000 | 90.26559759844687 |                         3 | テストデータ1_中国語                                                                                            |
    |  2 | f4975f27-e07a-4389-b615-a566fa66ba09 | 35.7301000 | 139.8301000 | 81.99640330604278 |                         1 | テストデータ2_日本語                                                                                            |
    |  3 | f4975f27-e07a-4389-b615-a566fa66ba10 | 35.7302000 | 139.8302000 | 75.55925354368239 |                         1 | テストデータ3_日本語                                                                                            |
    |  4 | f4975f27-e07a-4389-b615-a566fa66ba11 | 35.7303000 | 139.8303000 | 71.45090627299126 |                         3 | テストデータ4_中国語                                                                                            |
    +----+--------------------------------------+------------+-------------+-------------------+---------------------------+-----------------------------------------------------------------------------------------------------------------+
    
[/code]

変更点は３箇所です。  
1\. LEFT JOIN  
LEFT JOIN を使うことで、指定したlanguage_code_idが無い場合、NULLで結合するようにしています。  
2\. IFNULL  
NULLで結合されたカラムに対して、DEFAULTとしてlanguage_code_id = 1(日本)を設定します  
3\. SELECT value ...  
DEFAULTが１となる抽出されたlanguage_code_idを使って、valueを取得

また、tableBには tableA_id と language_code_idに対して
複合UNIQUE制約をつけたほうがいいです。(でない場合、LIMIT 1で返る値が変わる可能性があります)

CASE WHENを使っての抽出もできるとは思います。

今回のSQLですが、コードで使うなら、シンプルなクエリを２つ実行したほうがわかりやすいかもしれません。


CC BY-SA 4.0
2019-05-14T04:01:50.887
54942
2019-05-14T04:11:31.317
2019-05-14T04:11:31.317
24823
24823
54923
answer