--
-- SQL statement for selecting various language pairs. NOTE: ilmentyma table
-- also has an ilmentyma column, which is slightly more phonetic for some
-- orthographies than is ilmentyma_p, which seems to be more standard.
-- 
-- Leaving in all statements will not produce as many rows as for a pair,
-- probably because these should be another type of join.
--
-- To switch, make sure that (a), (b), and (c) has matching statements for each
-- table or alias.

SELECT DISTINCT ilmentyma.ilmentyma_p
              
              -- (a)

              -- , nielsen.ilmentyma_p
              -- , english.merkitys
              , finnish.merkitys
              -- , deutsch.merkitys

FROM (
   ilmentyma ilmentyma

               -- (b)

               -- LEFT JOIN `ilmentyma` AS nielsen
               --           ON nielsen.lekseemi_id = ilmentyma.lekseemi_id
               
               LEFT JOIN `lekseemi` AS lekseemi
                         ON ilmentyma.lekseemi_id = lekseemi.id
                         
               -- LEFT JOIN `merkitys` AS english
               --           ON lekseemi.id = english.lekseemi_id
               
               LEFT JOIN `merkitys` AS finnish
                         ON lekseemi.id = finnish.lekseemi_id
                
               -- LEFT JOIN `merkitys` AS deutsch
               --           ON lekseemi.id = deutsch.lekseemi_id
               
)

WHERE ( ilmentyma.ortognro = 2
    AND ilmentyma.varmennettu = 1

    -- AND nielsen.ortognro = 1

    -- (c)

    -- AND english.ortognro = 1
    -- AND english.varmennettu = 1
    
    AND finnish.ortognro = 2
    AND finnish.varmennettu = 1
    
    -- AND deutsch.ortognro = 4
    -- AND deutsch.varmennettu = 1

    AND lekseemi.kieli_id = 45 -- sme
) 

ORDER BY ilmentyma.ilmentyma ;
