質問: `tableB`.`tableA_id` HAVING distance <= 100.0 ORDER BY distance; ``` --tableAテストデータ--- ``` 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'); ``` --tableBテストデータ-- ``` 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'); ``` -- 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_中国語' になります。
回答: 改善できるクエリだと思いますが、一応やりたい事は機能しているようなので、回答します。 ``` 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; ``` 結果 ``` +----+--------------------------------------+------------+-------------+-------------------+---------------------------+-----------------------------------------------------------------------------------------------------------------+ | 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_中国語 | +----+--------------------------------------+------------+-------------+-------------------+---------------------------+-----------------------------------------------------------------------------------------------------------------+ ``` 変更点は3箇所です。 1. LEFT JOIN LEFT JOIN を使うことで、指定したlanguage_code_idが無い場合、NULLで結合するようにしています。 2. IFNULL NULLで結合されたカラムに対して、DEFAULTとしてlanguage_code_id = 1(日本)を設定します 3. SELECT value ... DEFAULTが1となる抽出されたlanguage_code_idを使って、valueを取得 また、tableBには tableA_id と language_code_idに対して 複合UNIQUE制約をつけたほうがいいです。(でない場合、LIMIT 1で返る値が変わる可能性があります) CASE WHENを使っての抽出もできるとは思います。 今回のSQLですが、コードで使うなら、シンプルなクエリを2つ実行したほうがわかりやすいかもしれません。