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

In edict an entry has reference to a reading element (r_ele) and eventually a kanji element (k_ele) In the database this translate to a join from table entry to table k_ele and r_ele.

If the search term is only kanji it should be searching into k_ele.keb. For example WHERE k_ele.keb = '辞典'

SELECT
    entry.id AS entry_id,
    sense.id AS sense_id,
    GROUP_CONCAT(DISTINCT COALESCE(k_ele.keb || ':', '') || r_ele.reb) keb_reb_group,
    GROUP_CONCAT(DISTINCT gloss.content) AS gloss_group,
    GROUP_CONCAT(DISTINCT pos.name) AS pos_group,
    GROUP_CONCAT(DISTINCT dial.name) AS dial_group,
    GROUP_CONCAT(DISTINCT misc.name) AS misc_group,
    GROUP_CONCAT(DISTINCT field.name) AS field_group,
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN sense_xref.reb IS NOT NULL
            THEN COALESCE(sense_xref.keb, '') || ':' || sense_xref.reb
            WHEN sense_xref.keb IS NOT NULL
            THEN sense_xref.keb
        END
    ) AS xref_group,
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN sense_ant.reb IS NOT NULL
            THEN COALESCE(sense_ant.keb, '') || ':' || sense_ant.reb
            WHEN sense_ant.keb IS NOT NULL
            THEN sense_ant.keb
        END
    ) AS ant_group
FROM entry
    JOIN r_ele ON entry.id = r_ele.id_entry
    JOIN sense ON sense.id_entry = entry.id
    JOIN gloss ON gloss.id_sense = sense.id
    LEFT JOIN k_ele ON entry.id = k_ele.id_entry
    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
    LEFT JOIN sense_xref ON sense.id = sense_xref.id_sense
    LEFT JOIN sense_ant ON sense.id = sense_ant.id_sense
WHERE r_ele.reb = 'あがる'
GROUP BY entry.id, 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 = "女"
);