Part 3. Query cookbook

This part lists example queries that are readily usable for common tasks. You can also use them as a starting point for writing your own queries.

Frequency lists

Number of poems by collector

SELECT c.name, count(*) as n
FROM
  p_col
  JOIN collectors c ON p_col.col_id = c.col_id
GROUP BY c.col_id
ORDER BY n DESC
LIMIT 30;

Number of poems by location

SELECT l.name, count(*) as n
FROM
  p_loc
  JOIN locations l ON p_loc.loc_id = l.loc_id
GROUP BY l.loc_id
ORDER BY n DESC
LIMIT 30;

Same, with county name if available:

SELECT lc.name as county, lp.name as parish, count(*) as n
FROM
  p_loc
  JOIN locations lp ON p_loc.loc_id = lp.loc_id
  LEFT JOIN locations lc ON lp.par_id = lc.loc_id
GROUP BY lp.loc_id
ORDER BY n DESC
LIMIT 30;

The county names are obtained by joining the locations table with itself: one copy of it refers to the parish (lp) and the other one to the county (lc). The join condition lp.par_id = lc.loc_id means that the county has to be the parent of the parish. We apply a LEFT JOIN here because not every location has a parent.

Same, but only for SKVR:

SELECT lc.name as county, lp.name as parish, count(*) as n
FROM
  p_loc
  JOIN locations lp ON p_loc.loc_id = lp.loc_id
  LEFT JOIN locations lc ON lp.par_id = lc.loc_id
  JOIN poems p ON p_loc.p_id = p.p_id
WHERE p.nro LIKE 'skvr%'
GROUP BY lp.loc_id
ORDER BY n DESC
LIMIT 30;

Length of poems

(number of verses in a poem)

SELECT p.nro, count(*) as n
FROM
  poems p
  JOIN verse_poem vp ON vp.p_id = p.p_id
GROUP BY p.p_id
LIMIT 30;

Same, but counting only <V> verses:

SELECT p.nro, count(*) as n
FROM
  poems p
  JOIN verse_poem vp ON vp.p_id = p.p_id
  JOIN verses v ON vp.v_id = v.v_id
WHERE v.type = 'V'
GROUP BY p.p_id
LIMIT 30;

Most common types

SELECT t.name, count(*) as n
FROM
  themes t
  JOIN poem_theme pt ON t.t_id = pt.t_id
GROUP BY t.t_id
ORDER BY n DESC
LIMIT 30;

Runoregi links

Multidiff comparisons of poems from the same parish assigned to the type Lunastettava neito:

SELECT
  lc.name, lp.name, t.name, count(*) AS n,
  CONCAT('http://runoregi.rahtiapp.fi/multidiff?nro=',
         GROUP_CONCAT(p.nro SEPARATOR ',')) AS link
FROM
  poems p
  JOIN poem_theme pt ON p.p_id = pt.p_id
  JOIN themes t ON pt.t_id = t.t_id
  JOIN p_loc ON p.p_id = p_loc.p_id
  JOIN locations lp ON p_loc.loc_id = lp.loc_id
  JOIN locations lc ON lp.par_id = lc.loc_id
WHERE t.name = 'Lunastettava neito'
GROUP BY lp.loc_id HAVING n > 2
LIMIT 10;