SQL recipes

expression.db

Search in english

SELECT
  entry.id AS entry_id,
  sense.id AS sense_id,
  (
    SELECT
      GROUP_CONCAT(IFNULL(keb || ':', '') || reb)
    FROM
      r_ele r_ele_sub
      LEFT JOIN r_ele_k_ele ON r_ele_k_ele.id_r_ele = r_ele_sub.id
      LEFT JOIN k_ele k_ele_sub ON r_ele_k_ele.id_k_ele = k_ele_sub.id
    WHERE
      r_ele_sub.id_entry = entry.id
  ) keb_reb_group,
  GROUP_CONCAT(DISTINCT gloss.content) gloss_group,
  GROUP_CONCAT(DISTINCT pos.name) pos_group,
  GROUP_CONCAT(DISTINCT dial.name) dial_group,
  GROUP_CONCAT(DISTINCT misc.name) misc_group,
  GROUP_CONCAT(DISTINCT field.name) field_group
FROM
  entry
  JOIN sense ON sense.id_entry = entry.id
  JOIN gloss ON gloss.id_sense = sense.id
  LEFT JOIN sense_pos ON sense.id = sense_pos.id_sense
  LEFT JOIN pos ON sense_pos.id_pos = pos.id
  LEFT JOIN sense_dial ON sense.id = sense_dial.id_sense
  LEFT JOIN dial ON sense_dial.id_dial = dial.id
  LEFT JOIN sense_misc ON sense.id = sense_misc.id_sense
  LEFT JOIN misc ON sense_misc.id_misc = misc.id
  LEFT JOIN sense_field ON sense.id = sense_field.id_sense
  LEFT JOIN field ON sense_field.id_field = field.id
WHERE
  entry.id IN (
    SELECT
      sense.id_entry
    FROM
      sense
      JOIN gloss ON gloss.id_sense = sense.id
    WHERE
      gloss.content = 'test'
  )
GROUP BY
  sense.id;

Search in Japanese

SELECT
  k_ele.id k_ele_id,
  entry.id AS entry_id,
  sense.id AS sense_id,
  (
    SELECT
      GROUP_CONCAT(IFNULL(keb || ':', '') || reb)
    FROM
      r_ele r_ele_sub
      LEFT JOIN r_ele_k_ele ON r_ele_k_ele.id_r_ele = r_ele_sub.id
      LEFT JOIN k_ele k_ele_sub ON r_ele_k_ele.id_k_ele = k_ele_sub.id
    WHERE
      r_ele_sub.id_entry = entry.id
  ) keb_reb_group,
  GROUP_CONCAT(DISTINCT gloss.content) gloss_group,
  GROUP_CONCAT(DISTINCT pos.name) pos_group,
  GROUP_CONCAT(DISTINCT dial.name) dial_group,
  GROUP_CONCAT(DISTINCT misc.name) misc_group,
  GROUP_CONCAT(DISTINCT field.name) field_group
FROM
  entry
  JOIN sense ON sense.id_entry = entry.id
  JOIN gloss ON gloss.id_sense = sense.id
  LEFT JOIN sense_pos ON sense.id = sense_pos.id_sense
  LEFT JOIN pos ON sense_pos.id_pos = pos.id
  LEFT JOIN sense_dial ON sense.id = sense_dial.id_sense
  LEFT JOIN dial ON sense_dial.id_dial = dial.id
  LEFT JOIN sense_misc ON sense.id = sense_misc.id_sense
  LEFT JOIN misc ON sense_misc.id_misc = misc.id
  LEFT JOIN sense_field ON sense.id = sense_field.id_sense
  LEFT JOIN field ON sense_field.id_field = field.id
  JOIN r_ele ON entry.id = r_ele.id_entry
  LEFT JOIN k_ele ON entry.id = k_ele.id_entry
WHERE
  reb = 'リョクトウ'
GROUP BY
  sense.id;

kanji.db

• Radicals, on and kun readings and english meanings for a given kanji

SELECT character.*, 
GROUP_CONCAT(DISTINCT character_radical.id_radical) as radicals, 
GROUP_CONCAT(DISTINCT on_yomi.reading) AS on_reading, 
GROUP_CONCAT(DISTINCT kun_yomi.reading) AS kun_reading, 
GROUP_CONCAT(DISTINCT meaning.content) AS meanings 
FROM character 
LEFT JOIN character_radical ON character.id = character_radical.id_character
LEFT JOIN on_yomi ON character.id = on_yomi.id_character
LEFT JOIN kun_yomi ON kun_yomi.id_character = character.id 
LEFT JOIN meaning ON meaning.id_character = character.id 
WHERE character.id="多";

• List all radicals

radical is a view of the kanji table, so we can have as many info on radicals as kanji.

For example, we can order by stroke

SELECT * FROM radical ORDER BY stroke_count;

• Get radical from kanji

For example, get radicals for the 思 kanji

SELECT radical.* 
FROM radical 
JOIN character_radical ON character_radical.id_radical = radical.id 
WHERE character_radical.id_character="思";

• Get Kanji from radicals

For example, get all Kanji where radicals are 二 and 女

For more radicals, intersect with as many wished radicals.

SELECT id 
FROM character 
WHERE id IN(SELECT id_character FROM character_radical WHERE id_radical = "二" INTERSECT 
            SELECT id_character FROM character_radical WHERE id_radical = "女"
);