join.sql

SELECT
  id, t1.name, model, body_name, color, engine, grade, inspection, recycle,
  year,
  country_name,
  price AS used_price,

  large_area_name, shop_pref_name, shop_lat, shop_lng, shop_name,t1.shop_pref_code,
  maintenance, maintenance_fee,

  odd, odd_numerics, odd_unit,
  IF( substr(odd_unit, 0, 2) = 'km', odd_numerics, odd_numerics*10000 ) AS odd_km,

  warranty, warranty_distance, warranty_fee, warranty_length_kigen, warranty_length_kikan,
  IF( substr(warranty_length_kikan, length(warranty_length_kikan), 1) = '年',
      regexp_extract(warranty_length_kikan, '([0-9]*)(.*)', 1) * 12,
      regexp_extract(warranty_length_kikan, '([0-9]*)(.*)', 1)
  ) AS warranty_length_kikan_m,

  main_photo_l, `desc`
FROM
(
    SELECT id, name, model, price, recycle, body_name, color, engine, grade, inspection, maintenance_fee, shop_pref_name, shop_pref_code, shop_lat, shop_lng, shop_name, maintenance, warranty, warranty_distance, warranty_fee, warranty_length_kigen, warranty_length_kikan, year, main_photo_l, `desc`,
    odd,
    CAST(regexp_extract(odd, '([\.0-9]*)(.*)', 1) AS FLOAT) AS odd_numerics,
    regexp_extract(odd, '([\.0-9]*)(.*)', 2) AS odd_unit
    FROM usedcar
) t1
JOIN
(
    SELECT country_name, name
    FROM brand_master
) t2
ON (t1.name = t2.name )
JOIN
(
    SELECT code, large_area_name
    FROM pref_master
) t3
ON ( t1.shop_pref_code = t3.code )