-- Media-UsersList3.sql
-- 2011-11-06 by ve3meo
-- Lists where media items are used and their key properties. Some SQLite managers will display thumbnail image (e.g. SQLite Expert).
-- The results displayed by some can be copied and pasted (or exported) to Excel which supports the synthesized Hyperlink column
-- for opening the media file.
-- 2011-11-15 extensively restructured to be more efficient on very large databases; order(s) of magnitude achieved!
--
SELECT   IFNULL( Thumbnail , 
  LOWER( SUBSTR( MediaFile , 
  -3 , 
  3 ) ) ) AS Thumbnail , 
  HyperLink , 
  MediaFile , 
  MediaPath , 
  OwnerID , 
  CASE OwnerType 
    WHEN 0 THEN '(person) ' 
    WHEN 1 THEN '(spouse) ' 
    WHEN 2 THEN '(event) ' 
    WHEN 3 THEN '(source) ' 
    WHEN 4 THEN '(citation) ' 
    WHEN 5 THEN '(place) ' 
    ELSE 'TBA' 
  END AS OwnerType , 
  CASE "Person1" IS NULL 
    WHEN 1 THEN '' 
    ELSE "Person1" || '-' || "RIN1" || ' : ' 
  END || CASE Fact IS NULL 
    WHEN 1 THEN '' 
    ELSE UPPER( Fact ) || ' ' 
  END || CASE EventDate IS NULL 
    WHEN 1 THEN '' 
    ELSE EventDate || ' ' 
  END || CASE "Person2" IS NULL 
    WHEN 1 THEN '' 
    ELSE ': ' || "Person2" || '-' || "RIN2" || ' ' 
  END || CASE "SourceName" IS NOT NULL AND OwnerType = 4 
    WHEN 1 THEN 'citing ' || SourceName 
    ELSE IFNULL( SourceName , 
    '' ) 
  END AS Owner , 
  Caption 
FROM 
  ( 
    SELECT 
      OwnerID , 
      OwnerType , 
      Fact , 
      SourceName , 
      "RIN1" , 
      UPPER( "Surname1" ) || CASE "Suffix1" -- Concatenate Name fields 
        WHEN '' THEN '' 
        ELSE ' ' || "Suffix1" 
      END || ', ' || CASE "Prefix1" 
        WHEN '' THEN '' 
        ELSE Prefix1 || ' ' 
      END || "Given1" || CASE 
        WHEN "Nickname1" NOT LIKE '' THEN ' "' || "Nickname1" || '"' 
        ELSE '' 
      END AS 'Person1' , 
      "MRIN" , 
      "RIN2" , 
      UPPER( "Surname2" ) || CASE "Suffix2" -- Concatenate Spouse Name fields 
        WHEN '' THEN '' 
        ELSE ' ' || "Suffix2" 
      END || ', ' || CASE "Prefix2" 
        WHEN '' THEN '' 
        ELSE Prefix2 || ' ' 
      END || "Given2" || CASE 
        WHEN "Nickname2" NOT LIKE '' THEN ' "' || "Nickname2" || '"' 
        ELSE '' 
      END AS 'Person2' , 
      CASE Substr( rawDate -- Convert Fact dates to readable form 
, 
      1 , 
      1 ) 
        WHEN 'Q' THEN rawDate 
        WHEN 'T' THEN Substr( rawDate , 
        2 , 
        20 ) 
        WHEN 'D' THEN CASE Substr( rawDate , 
        2 , 
        1 ) 
          WHEN 'A' THEN 'aft ' 
          WHEN 'B' THEN 'bef ' 
          WHEN 'F' THEN 'from ' 
          WHEN 'I' THEN 'since ' 
          WHEN 'R' THEN 'bet ' 
          WHEN 'S' THEN 'from ' 
          WHEN 'T' THEN 'to ' 
          WHEN 'U' THEN 'until ' 
          WHEN 'Y' THEN 'by ' 
          ELSE '' 
        END || CASE Substr( rawDate , 
        13 , 
        1 ) 
          WHEN 'A' THEN 'abt ' 
          WHEN 'C' THEN 'ca ' 
          WHEN 'E' THEN 'est ' 
          WHEN 'L' THEN 'calc ' 
          WHEN 'S' THEN 'say ' 
          WHEN '6' THEN 'cert ' 
          WHEN '5' THEN 'prob ' 
          WHEN '4' THEN 'poss ' 
          WHEN '3' THEN 'lkly ' 
          WHEN '2' THEN 'appar ' 
          WHEN '1' THEN 'prhps ' 
          WHEN '?' THEN 'maybe ' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawDate , 
          3 , 
          1 ) = '-' THEN 'BC' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawDate , 
          4 , 
          4 ) = '0000' THEN '' 
          ELSE Substr( rawDate , 
          4 , 
          4 ) 
        END || CASE 
          WHEN Substr( rawDate , 
          12 , 
          1 ) = '/' THEN '/' || ( 
            1 + Substr( rawDate , 
            3 , 
            5 ) 
          ) 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawDate , 
          8 , 
          2 ) = '00' AND ( 
            Substr( rawDate , 
            4 , 
            4 ) <> '0000' AND Substr( rawDate , 
            10 , 
            2 ) <> '00' 
          ) THEN '-??' 
          WHEN Substr( rawDate , 
          8 , 
          2 ) = '00' AND ( 
            Substr( rawDate , 
            4 , 
            4 ) <> '0000' AND Substr( rawDate , 
            10 , 
            2 ) == '00' 
          ) THEN '' 
          WHEN Substr( rawDate , 
          8 , 
          2 ) = '00' THEN '' 
          ELSE '-' || Substr( rawDate , 
          8 , 
          2 ) 
        END || Coalesce( Nullif( '-' || Substr( rawDate , 
        10 , 
        2 ) , 
        '-00' ) , 
        '' ) || CASE Substr( rawDate , 
        2 , 
        1 ) 
          WHEN 'R' THEN ' and ' 
          WHEN 'S' THEN ' to ' 
          WHEN 'O' THEN ' or ' 
          WHEN '-' THEN ' - ' 
          ELSE '' 
        END || CASE Substr( rawDate , 
        24 , 
        1 ) 
          WHEN 'A' THEN 'abt ' 
          WHEN 'E' THEN 'est ' 
          WHEN 'L' THEN 'calc ' 
          WHEN 'C' THEN 'ca ' 
          WHEN 'S' THEN 'say ' 
          WHEN '6' THEN 'cert ' 
          WHEN '5' THEN 'prob ' 
          WHEN '4' THEN 'poss ' 
          WHEN '3' THEN 'lkly ' 
          WHEN '2' THEN 'appar ' 
          WHEN '1' THEN 'prhps ' 
          WHEN '?' THEN 'maybe ' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawDate , 
          14 , 
          1 ) = '-' THEN 'BC' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawDate , 
          15 , 
          4 ) = '0000' THEN '' 
          ELSE Substr( rawDate , 
          15 , 
          4 ) 
        END || CASE 
          WHEN Substr( rawDate , 
          23 , 
          1 ) = '/' THEN '/' || ( 
            1 + Substr( rawDate , 
            14 , 
            5 ) 
          ) 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawDate , 
          19 , 
          2 ) = '00' AND ( 
            Substr( rawDate , 
            15 , 
            4 ) <> '0000' AND Substr( rawDate , 
            21 , 
            2 ) <> '00' 
          ) THEN '-??' 
          WHEN Substr( rawDate , 
          19 , 
          2 ) = '00' AND ( 
            Substr( rawDate , 
            15 , 
            4 ) <> '0000' AND Substr( rawDate , 
            21 , 
            2 ) == '00' 
          ) THEN '' 
          WHEN Substr( rawDate , 
          19 , 
          2 ) = '00' THEN '' 
          ELSE '-' || Substr( rawDate , 
          19 , 
          2 ) 
        END || Coalesce( Nullif( '-' || Substr( rawDate , 
        21 , 
        2 ) , 
        '-00' ) , 
        '' ) 
        ELSE '' 
      END AS 'EventDate' , 
      MediaID , 
      Thumbnail , 
      MediaPath , 
      MediaFile , 
      '=HYPERLINK("' || MediaPath || MediaFile || '","Open")' AS HyperLink , 
      Caption , 
      Description , 
      CASE SUBSTR( rawMDate -- Convert Media dates to readable form 
, 
      1 , 
      1 ) 
        WHEN 'Q' THEN rawMDate 
        WHEN 'T' THEN Substr( rawMDate , 
        2 , 
        20 ) 
        WHEN 'D' THEN CASE Substr( rawMDate , 
        2 , 
        1 ) 
          WHEN 'A' THEN 'aft ' 
          WHEN 'B' THEN 'bef ' 
          WHEN 'F' THEN 'from ' 
          WHEN 'I' THEN 'since ' 
          WHEN 'R' THEN 'bet ' 
          WHEN 'S' THEN 'from ' 
          WHEN 'T' THEN 'to ' 
          WHEN 'U' THEN 'until ' 
          WHEN 'Y' THEN 'by ' 
          ELSE '' 
        END || CASE Substr( rawMDate , 
        13 , 
        1 ) 
          WHEN 'A' THEN 'abt ' 
          WHEN 'C' THEN 'ca ' 
          WHEN 'E' THEN 'est ' 
          WHEN 'L' THEN 'calc ' 
          WHEN 'S' THEN 'say ' 
          WHEN '6' THEN 'cert ' 
          WHEN '5' THEN 'prob ' 
          WHEN '4' THEN 'poss ' 
          WHEN '3' THEN 'lkly ' 
          WHEN '2' THEN 'appar ' 
          WHEN '1' THEN 'prhps ' 
          WHEN '?' THEN 'maybe ' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawMDate , 
          3 , 
          1 ) = '-' THEN 'BC' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawMDate , 
          4 , 
          4 ) = '0000' THEN '' 
          ELSE Substr( rawMDate , 
          4 , 
          4 ) 
        END || CASE 
          WHEN Substr( rawMDate , 
          12 , 
          1 ) = '/' THEN '/' || ( 
            1 + Substr( rawMDate , 
            3 , 
            5 ) 
          ) 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawMDate , 
          8 , 
          2 ) = '00' AND ( 
            Substr( rawMDate , 
            4 , 
            4 ) <> '0000' AND Substr( rawMDate , 
            10 , 
            2 ) <> '00' 
          ) THEN '-??' 
          WHEN Substr( rawMDate , 
          8 , 
          2 ) = '00' AND ( 
            Substr( rawMDate , 
            4 , 
            4 ) <> '0000' AND Substr( rawMDate , 
            10 , 
            2 ) == '00' 
          ) THEN '' 
          WHEN Substr( rawMDate , 
          8 , 
          2 ) = '00' THEN '' 
          ELSE '-' || Substr( rawMDate , 
          8 , 
          2 ) 
        END || Coalesce( Nullif( '-' || Substr( rawMDate , 
        10 , 
        2 ) , 
        '-00' ) , 
        '' ) || CASE Substr( rawMDate , 
        2 , 
        1 ) 
          WHEN 'R' THEN ' and ' 
          WHEN 'S' THEN ' to ' 
          WHEN 'O' THEN ' or ' 
          WHEN '-' THEN ' - ' 
          ELSE '' 
        END || CASE Substr( rawMDate , 
        24 , 
        1 ) 
          WHEN 'A' THEN 'abt ' 
          WHEN 'E' THEN 'est ' 
          WHEN 'L' THEN 'calc ' 
          WHEN 'C' THEN 'ca ' 
          WHEN 'S' THEN 'say ' 
          WHEN '6' THEN 'cert ' 
          WHEN '5' THEN 'prob ' 
          WHEN '4' THEN 'poss ' 
          WHEN '3' THEN 'lkly ' 
          WHEN '2' THEN 'appar ' 
          WHEN '1' THEN 'prhps ' 
          WHEN '?' THEN 'maybe ' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawMDate , 
          14 , 
          1 ) = '-' THEN 'BC' 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawMDate , 
          15 , 
          4 ) = '0000' THEN '' 
          ELSE Substr( rawMDate , 
          15 , 
          4 ) 
        END || CASE 
          WHEN Substr( rawMDate , 
          23 , 
          1 ) = '/' THEN '/' || ( 
            1 + Substr( rawMDate , 
            14 , 
            5 ) 
          ) 
          ELSE '' 
        END || CASE 
          WHEN Substr( rawMDate , 
          19 , 
          2 ) = '00' AND ( 
            Substr( rawMDate , 
            15 , 
            4 ) <> '0000' AND Substr( rawMDate , 
            21 , 
            2 ) <> '00' 
          ) THEN '-??' 
          WHEN Substr( rawMDate , 
          19 , 
          2 ) = '00' AND ( 
            Substr( rawMDate , 
            15 , 
            4 ) <> '0000' AND Substr( rawMDate , 
            21 , 
            2 ) == '00' 
          ) THEN '' 
          WHEN Substr( rawMDate , 
          19 , 
          2 ) = '00' THEN '' 
          ELSE '-' || Substr( rawMDate , 
          19 , 
          2 ) 
        END || Coalesce( Nullif( '-' || Substr( rawMDate , 
        21 , 
        2 ) , 
        '-00' ) , 
        '' ) 
        ELSE '' 
      END AS 'MediaDate' --
, 
      RefNumber , 
      Include1 AS Scrpbk , 
      IsPrimary 
    FROM -- Owner is a person
-- OwnerType 0 in MediaLinkTable
      ( 
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Owner is a person
-- OwnerType 0 in MediaLinkTable
              N.OwnerID AS OwnerID , 
              0 AS OwnerType , 
              N.OwnerID COLLATE NOCASE AS 'RIN1' , 
              N.Surname COLLATE NOCASE AS 'Surname1' , 
              N.Suffix COLLATE NOCASE AS 'Suffix1' , 
              N.Prefix COLLATE NOCASE AS 'Prefix1' , 
              N.Given COLLATE NOCASE AS 'Given1' , 
              N.Nickname COLLATE NOCASE AS 'Nickname1' , 
              'Person' AS 'Fact' , 
              NULL AS 'rawDate' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' , 
              NULL AS 'SourceName' 
            FROM 
              NameTable AS N 
            WHERE 
              +N.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL -- Owner is a couple or family
-- OwnerType=1 (family) in MediaLinkTable
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Owner is a couple or family
-- OwnerType=1 (family) in MediaLinkTable
              F.FamilyID AS OwnerID , 
              1 AS OwnerType , 
              N1.OwnerID AS 'RIN1' , 
              N1.Surname COLLATE NOCASE AS 'Surname1' , 
              N1.Suffix COLLATE NOCASE AS 'Suffix1' , 
              N1.Prefix COLLATE NOCASE AS 'Prefix1' , 
              N1.Given COLLATE NOCASE AS 'Given1' , 
              N1.Nickname COLLATE NOCASE AS 'Nickname1' , 
              'Spouse' AS 'Fact' , 
              NULL AS 'rawDate' , 
              N2.OwnerID AS 'RIN2' , 
              N2.Surname COLLATE NOCASE AS 'Surname2' , 
              N2.Suffix COLLATE NOCASE AS 'Suffix2' , 
              N2.Prefix COLLATE NOCASE AS 'Prefix2' , 
              N2.Given COLLATE NOCASE AS 'Given2' , 
              N2.Nickname COLLATE NOCASE AS 'Nickname2' , 
              NULL AS 'SourceName' 
            FROM 
              FamilyTable AS F INNER JOIN 
              NameTable AS N1 ON F.FatherID = N1.OwnerID INNER JOIN 
              NameTable AS N2 ON F.MotherID = N2.OwnerID 
            WHERE 
              +N1.IsPrimary = 1 AND +N2.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL -- Owner is a couple or family in REVERSE order
-- OwnerType=1 (family) in MediaLinkTable
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Owner is a couple or family in REVERSE order
-- OwnerType=1 (family) in MediaLinkTable
              F.FamilyID AS OwnerID , 
              1 AS OwnerType , 
              N1.OwnerID AS 'RIN1' , 
              N1.Surname COLLATE NOCASE AS 'Surname1' , 
              N1.Suffix COLLATE NOCASE AS 'Suffix1' , 
              N1.Prefix COLLATE NOCASE AS 'Prefix1' , 
              N1.Given COLLATE NOCASE AS 'Given1' , 
              N1.Nickname COLLATE NOCASE AS 'Nickname1' , 
              'Spouse' AS 'Fact' , 
              NULL AS 'rawDate' , 
              N2.OwnerID AS 'RIN2' , 
              N2.Surname COLLATE NOCASE AS 'Surname2' , 
              N2.Suffix COLLATE NOCASE AS 'Suffix2' , 
              N2.Prefix COLLATE NOCASE AS 'Prefix2' , 
              N2.Given COLLATE NOCASE AS 'Given2' , 
              N2.Nickname COLLATE NOCASE AS 'Nickname2' , 
              NULL AS 'SourceName' 
            FROM 
              FamilyTable AS F INNER JOIN 
              NameTable AS N1 ON F.MotherID = N1.OwnerID INNER JOIN 
              NameTable AS N2 ON F.FatherID = N2.OwnerID 
            WHERE 
              +N1.IsPrimary = 1 AND +N2.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL -- Owner is a fact/event for a person
-- person events (ML.OwnerType=2, Ev.OwnerType=0)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Owner is a fact/event for a person
-- person events (ML.OwnerType=2, Ev.OwnerType=0)
              Ev.EventID AS OwnerID , 
              2 AS OwnerType , 
              N.OwnerID COLLATE NOCASE AS 'RIN1' , 
              N.Surname COLLATE NOCASE AS 'Surname1' , 
              N.Suffix COLLATE NOCASE AS 'Suffix1' , 
              N.Prefix COLLATE NOCASE AS 'Prefix1' , 
              N.Given COLLATE NOCASE AS 'Given1' , 
              N.Nickname COLLATE NOCASE AS 'Nickname1' , 
              FT.Name COLLATE NOCASE AS 'Fact' , 
              Ev.Date AS 'rawDate' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' , 
              NULL AS 'SourceName' 
            FROM 
              EventTable Ev INNER JOIN 
              FactTypeTable FT ON Ev.EventType = FT.FactTypeID INNER JOIN 
              NameTable N ON Ev.OwnerID = N.OwnerID 
            WHERE 
              +N.IsPrimary = 1 AND Ev.OwnerType = 0 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL -- Owner is an event for a couple or family 
-- family events (ML.OwnerType=2, Ev.OwnerType=1) 
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Owner is an event for a couple or family 
-- family events (ML.OwnerType=2, Ev.OwnerType=1)
              Ev.EventID AS OwnerID , 
              2 AS OwnerType , 
              N1.OwnerID AS 'RIN1' , 
              N1.Surname COLLATE NOCASE AS 'Surname1' , 
              N1.Suffix COLLATE NOCASE AS 'Suffix1' , 
              N1.Prefix COLLATE NOCASE AS 'Prefix1' , 
              N1.Given COLLATE NOCASE AS 'Given1' , 
              N1.Nickname COLLATE NOCASE AS 'Nickname1' , 
              FT.Name COLLATE NOCASE AS 'Fact' , 
              Ev.Date AS 'rawDate' , 
              N2.OwnerID AS 'RIN2' , 
              N2.Surname COLLATE NOCASE AS 'Surname2' , 
              N2.Suffix COLLATE NOCASE AS 'Suffix2' , 
              N2.Prefix COLLATE NOCASE AS 'Prefix2' , 
              N2.Given COLLATE NOCASE AS 'Given2' , 
              N2.Nickname COLLATE NOCASE AS 'Nickname2' , 
              NULL AS 'SourceName' 
            FROM 
              EventTable Ev INNER JOIN 
              FactTypeTable FT ON Ev.EventType = FT.FactTypeID INNER JOIN 
              FamilyTable F ON Ev.OwnerID = F.FamilyID INNER JOIN 
              NameTable AS N1 ON F.FatherID = N1.OwnerID INNER JOIN 
              NameTable AS N2 ON F.MotherID = N2.OwnerID 
            WHERE 
              +N1.IsPrimary = 1 AND +N2.IsPrimary = 1 AND Ev.OwnerType = 1 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL -- Owner is an event for a couple or family in REVERSE order 
-- family events (ML.OwnerType=2, Ev.OwnerType=1) 
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Owner is an event for a couple or family in REVERSE order 
-- family events (ML.OwnerType=2, Ev.OwnerType=1)
              Ev.EventID AS OwnerID , 
              2 AS OwnerType , 
              N1.OwnerID AS 'RIN1' , 
              N1.Surname COLLATE NOCASE AS 'Surname1' , 
              N1.Suffix COLLATE NOCASE AS 'Suffix1' , 
              N1.Prefix COLLATE NOCASE AS 'Prefix1' , 
              N1.Given COLLATE NOCASE AS 'Given1' , 
              N1.Nickname COLLATE NOCASE AS 'Nickname1' , 
              FT.Name COLLATE NOCASE AS 'Fact' , 
              Ev.Date AS 'rawDate' , 
              N2.OwnerID AS 'RIN2' , 
              N2.Surname COLLATE NOCASE AS 'Surname2' , 
              N2.Suffix COLLATE NOCASE AS 'Suffix2' , 
              N2.Prefix COLLATE NOCASE AS 'Prefix2' , 
              N2.Given COLLATE NOCASE AS 'Given2' , 
              N2.Nickname COLLATE NOCASE AS 'Nickname2' , 
              NULL AS 'SourceName' 
            FROM 
              EventTable Ev INNER JOIN 
              FactTypeTable FT ON Ev.EventType = FT.FactTypeID INNER JOIN 
              FamilyTable F ON Ev.OwnerID = F.FamilyID INNER JOIN 
              NameTable AS N1 ON F.MotherID = N1.OwnerID INNER JOIN 
              NameTable AS N2 ON F.FatherID = N2.OwnerID 
            WHERE 
              +N1.IsPrimary = 1 AND +N2.IsPrimary = 1 AND Ev.OwnerType = 1 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType -- END of Media used by Facts/Events    
            ) 
        UNION ALL -- owner is a Master Source 
-- ML.OwnerType=3 (master source) 
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- owner is a Master Source 
-- ML.OwnerType=3 (master source)
              SourceID AS OwnerID , 
              3 AS OwnerType , 
              NULL AS 'RIN1' , 
              NULL AS 'Surname1' , 
              NULL AS 'Suffix1' , 
              NULL AS 'Prefix1' , 
              NULL AS 'Given1' , 
              NULL AS 'Nickname1' , 
              NULL AS 'Fact' , 
              NULL AS 'rawDate' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' , 
              Name COLLATE NOCASE AS 'SourceName' 
            FROM 
              SourceTable 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL -- Media used by Citations
-- Owner is a citation for a person
-- person (ML.OwnerType=4, CitationTable.OwnerType = 0)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Person Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Person' AS 'Citation Type' , 
              '(Person)' AS 'Fact' , 
              NULL AS 'rawDate' , 
              NULL AS MRIN , 
              NameTable.OwnerID AS 'RIN1' , 
              NameTable.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable.Given COLLATE NOCASE AS 'Given1' , 
              NameTable.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              NameTable ON CitationTable.OwnerID = NameTable.OwnerID 
            WHERE 
              CitationTable.OwnerType = 0 AND +NameTable.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 
        UNION ALL -- Owner is a citation for a Family
-- person (ML.OwnerType=4, CitationTable.OwnerType = 1)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Family Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Family' AS 'Citation Type' , 
              '(Family)' AS 'Fact' , 
              NULL AS 'rawDate' , 
              FamilyTable.FamilyID AS MRIN , 
              NameTable1.OwnerID AS 'RIN1' , 
              NameTable1.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable1.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable1.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable1.Given COLLATE NOCASE AS 'Given1' , 
              NameTable1.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NameTable2.OwnerID AS 'RIN2' , 
              NameTable2.Surname COLLATE NOCASE AS 'Surname2' , 
              NameTable2.Suffix COLLATE NOCASE AS 'Suffix2' , 
              NameTable2.Prefix COLLATE NOCASE AS 'Prefix2' , 
              NameTable2.Given COLLATE NOCASE AS 'Given2' , 
              NameTable2.Nickname COLLATE NOCASE AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              FamilyTable ON CitationTable.OwnerID = FamilyTable.FamilyID INNER JOIN 
              NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN 
              NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID 
            WHERE 
              CitationTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 
        UNION ALL -- Owner is a citation for a Family in Reverse order
-- person (ML.OwnerType=4, CitationTable.OwnerType = 1)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Reverse Family Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Family' AS 'Citation Type' , 
              '(Family)' AS 'Fact' , 
              NULL AS 'rawDate' , 
              FamilyTable.FamilyID AS MRIN , 
              NameTable2.OwnerID AS 'RIN1' , 
              NameTable2.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable2.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable2.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable2.Given COLLATE NOCASE AS 'Given1' , 
              NameTable2.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NameTable1.OwnerID AS 'RIN2' , 
              NameTable1.Surname COLLATE NOCASE AS 'Surname2' , 
              NameTable1.Suffix COLLATE NOCASE AS 'Suffix2' , 
              NameTable1.Prefix COLLATE NOCASE AS 'Prefix2' , 
              NameTable1.Given COLLATE NOCASE AS 'Given2' , 
              NameTable1.Nickname COLLATE NOCASE AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              FamilyTable ON CitationTable.OwnerID = FamilyTable.FamilyID INNER JOIN 
              NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN 
              NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID 
            WHERE 
              CitationTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 
        UNION ALL -- Owner is a citation for a Fact for a Person
-- person (ML.OwnerType=4, CitationTable.OwnerType = 2, EventTable.OwnerType = 0)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Person Fact Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Fact - Person' AS 'Citation Type' , 
              FactTypeTable.Name COLLATE NOCASE AS 'Fact' , 
              EventTable.Date AS 'rawDate' , 
              NULL AS MRIN , 
              NameTable.OwnerID AS 'RIN1' , 
              NameTable.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable.Given COLLATE NOCASE AS 'Given1' , 
              NameTable.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              EventTable ON CitationTable.OwnerID = EventTable.EventID INNER JOIN 
              FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN 
              NameTable ON EventTable.OwnerID = NameTable.OwnerID 
            WHERE 
              CitationTable.OwnerType = 2 AND +NameTable.IsPrimary = 1 AND EventTable.OwnerType = 0 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 
        UNION ALL -- Owner is a citation for a Fact for a Family
-- person (ML.OwnerType=4, CitationTable.OwnerType = 2, EventTable.OwnerType = 1)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Family Fact Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Fact - Family' AS 'Citation Type' , 
              FactTypeTable.Name COLLATE NOCASE AS 'Fact' , 
              EventTable.Date AS 'rawDate' , 
              FamilyTable.FamilyID AS MRIN , 
              NameTable1.OwnerID AS 'RIN1' , 
              NameTable1.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable1.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable1.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable1.Given COLLATE NOCASE AS 'Given1' , 
              NameTable1.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NameTable2.OwnerID AS 'RIN2' , 
              NameTable2.Surname COLLATE NOCASE AS 'Surname2' , 
              NameTable2.Suffix COLLATE NOCASE AS 'Suffix2' , 
              NameTable2.Prefix COLLATE NOCASE AS 'Prefix2' , 
              NameTable2.Given COLLATE NOCASE AS 'Given2' , 
              NameTable2.Nickname COLLATE NOCASE AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              EventTable ON CitationTable.OwnerID = EventTable.EventID INNER JOIN 
              FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN 
              FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID INNER JOIN 
              NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN 
              NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID 
            WHERE 
              CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 
        UNION ALL -- Owner is a citation for a Fact for a Family
-- person (ML.OwnerType=4, CitationTable.OwnerType = 2, EventTable.OwnerType = 1)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Reverse Family Fact Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Fact - Family' AS 'Citation Type' , 
              FactTypeTable.Name COLLATE NOCASE AS 'Fact' , 
              EventTable.Date AS 'rawDate' , 
              FamilyTable.FamilyID AS MRIN , 
              NameTable2.OwnerID AS 'RIN1' , 
              NameTable2.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable2.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable2.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable2.Given COLLATE NOCASE AS 'Given1' , 
              NameTable2.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NameTable1.OwnerID AS 'RIN2' , 
              NameTable1.Surname COLLATE NOCASE AS 'Surname2' , 
              NameTable1.Suffix COLLATE NOCASE AS 'Suffix2' , 
              NameTable1.Prefix COLLATE NOCASE AS 'Prefix2' , 
              NameTable1.Given COLLATE NOCASE AS 'Given2' , 
              NameTable1.Nickname COLLATE NOCASE AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              EventTable ON CitationTable.OwnerID = EventTable.EventID INNER JOIN 
              FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN 
              FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID INNER JOIN 
              NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN 
              NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID 
            WHERE 
              CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 
        UNION ALL -- Owner is a citation for an Alternate Name Fact
-- person (ML.OwnerType=4, CitationTable.OwnerType = 7)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          CAST( 4 AS INTEGER ) AS OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- Alternate Name Fact Citations
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              CitationTable.CitationID AS 'OwnerID' , 
              'Fact - Alt Name' AS 'Citation Type' , 
              'Alt Name' AS 'Fact' , 
              NameTable1.Date AS 'rawDate' , 
              NULL AS MRIN , 
              NameTable2.OwnerID AS 'RIN1' , 
              NameTable2.Surname COLLATE NOCASE AS 'Surname1' , 
              NameTable2.Suffix COLLATE NOCASE AS 'Suffix1' , 
              NameTable2.Prefix COLLATE NOCASE AS 'Prefix1' , 
              NameTable2.Given COLLATE NOCASE AS 'Given1' , 
              NameTable2.Nickname COLLATE NOCASE AS 'Nickname1' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) INNER JOIN 
              NameTable AS NameTable1 ON CitationTable.OwnerID = NameTable1.NameID INNER JOIN 
              NameTable AS NameTable2 ON NameTable1.OwnerID = NameTable2.OwnerID 
            WHERE 
              CitationTable.OwnerType = 7 AND +NameTable1.IsPrimary = 0 AND +NameTable2.IsPrimary = 1 
          ) AS O USING 
            ( 
              OwnerID 
            ) 
        WHERE 
          ML.OwnerType = 4 --
-- end of the core UNION of 7 SELECT queries to collect all citations using media
-- END OF Citations Query
        UNION ALL --
-- owner is a Place Detail 
-- ML.OwnerType=5 (place or place detail) 
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- owner is a Place Detail 
-- ML.OwnerType=5 (place or place detail)
              P2.PlaceID AS OwnerID , 
              5 AS OwnerType , 
              NULL AS 'RIN1' , 
              NULL AS 'Surname1' , 
              NULL AS 'Suffix1' , 
              NULL AS 'Prefix1' , 
              NULL AS 'Given1' , 
              NULL AS 'Nickname1' , 
              'Place Detail' AS 'Fact' , 
              NULL AS 'rawDate' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' , 
              P2.Name COLLATE NOCASE || ' : ' || P1.Name COLLATE NOCASE AS 'SourceName' 
            FROM 
              PlaceTable P2 LEFT JOIN 
              PlaceTable P1 ON P2.MasterID = P1.PlaceID 
            WHERE 
              P2.PlaceType = 2 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
        UNION ALL --
-- owner is a Place 
-- ML.OwnerType=5 (place or place detail)
        SELECT -- All fields and values from MediaLinkTable, MultiMediaTable and O query
          MM.MediaID , 
          MM.MediaType , 
          MM.MediaPath , 
          MM.MediaFile COLLATE NOCASE , 
          MM.URL , 
          MM.Thumbnail , 
          ML.LinkID , 
          ML.MediaID , 
          ML.OwnerType , 
          ML.OwnerID , 
          ML.IsPrimary , 
          ML.Include1 , 
          ML.Include2 , 
          ML.Include3 , 
          ML.Include4 , 
          ML.SortOrder , 
          ML.RectLeft , 
          ML.RectTop , 
          ML.RectRight , 
          ML.RectBottom , 
          ML.Note , 
          ML.Caption COLLATE NOCASE , 
          ML.RefNumber COLLATE NOCASE , 
          ML.Date AS rawMDate , 
          ML.SortDate , 
          CAST( ML.Description AS TEXT ) AS Description , 
          O.OwnerID , 
          O.OwnerType , 
          O.RIN1 , 
          O.Surname1 , 
          O.Suffix1 , 
          O.Prefix1 , 
          O.Given1 , 
          O.Nickname1 , 
          O.Fact , 
          O.rawDate , 
          O.RIN2 , 
          O.Surname2 , 
          O.Suffix2 , 
          O.Prefix2 , 
          O.Given2 , 
          O.Nickname2 , 
          O.SourceName 
        FROM 
          MultiMediaTable AS MM LEFT JOIN 
          MediaLinkTable AS ML USING 
            ( 
              MediaID 
            ) INNER JOIN 
          ( 
            SELECT -- owner is a Place 
-- ML.OwnerType=5 (place or place detail)
              P1.PlaceID AS OwnerID , 
              5 AS OwnerType , 
              NULL AS 'RIN1' , 
              NULL AS 'Surname1' , 
              NULL AS 'Suffix1' , 
              NULL AS 'Prefix1' , 
              NULL AS 'Given1' , 
              NULL AS 'Nickname1' , 
              'Place' AS 'Fact' , 
              NULL AS 'rawDate' , 
              NULL AS 'RIN2' , 
              NULL AS 'Surname2' , 
              NULL AS 'Suffix2' , 
              NULL AS 'Prefix2' , 
              NULL AS 'Given2' , 
              NULL AS 'Nickname2' , 
              P1.Name COLLATE NOCASE AS 'SourceName' 
            FROM 
              PlaceTable P1 
            WHERE 
              P1.PlaceType <> 2 
          ) AS O USING 
            ( 
              OwnerID , 
              OwnerType 
            ) 
      ) 
  ) 
ORDER BY 
  MediaFile , 
  Owner ;