-- Media-UsersList.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.
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 
      ( 
        SELECT -- All fields and values from MediaLinkTable and MultiMediaTable
          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 
        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 
        UNION ALL 
        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 
        UNION ALL 
        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 
        UNION ALL 
        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 
        UNION ALL 
        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 
        UNION ALL 
        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 
        UNION ALL 
        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 AS 'SourceName' 
        FROM 
          SourceTable 
        UNION ALL 
        SELECT -- owner is a citation 
-- ML.OwnerType=4 (citation)
          citID AS OwnerID , 
          4 AS OwnerType , 
          RIN1 , 
          Surname1 , 
          Suffix1 , 
          Prefix1 , 
          Given1 , 
          Nickname1 , 
          FactType AS Fact , 
          rawDate , 
          RIN2 , 
          Surname2 , 
          Suffix2 , 
          Prefix2 , 
          Given2 , 
          Nickname2 , 
          SourceName 
        FROM -- Start of UNION of 7 SELECT queries of the database to collect all citations
          ( 
            SELECT -- Person Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Person' AS 'Citation Type' , 
              '(Person)' AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) INNER JOIN 
              NameTable ON CitationTable.OwnerID = NameTable.OwnerID 
            WHERE 
              CitationTable.OwnerType = 0 AND +NameTable.IsPrimary = 1 
            GROUP BY 
              "CitID" 
            UNION ALL 
            SELECT -- Family Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Family' AS 'Citation Type' , 
              '(Family)' AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) 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 
            GROUP BY 
              "CitID" 
            UNION ALL 
            SELECT -- Reverse Family Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Family' AS 'Citation Type' , 
              '(Family)' AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) 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 
            GROUP BY 
              "CitID" 
            UNION ALL 
            SELECT -- Person Fact Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Fact - Person' AS 'Citation Type' , 
              FactTypeTable.Name COLLATE NOCASE AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) 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 
            GROUP BY 
              "CitID" 
            UNION ALL 
            SELECT -- Family Fact Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Fact - Family' AS 'Citation Type' , 
              FactTypeTable.Name COLLATE NOCASE AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) 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 
            GROUP BY 
              "CitID" 
            UNION ALL 
            SELECT -- Reverse Family Fact Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Fact - Family' AS 'Citation Type' , 
              FactTypeTable.Name COLLATE NOCASE AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) 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 
            GROUP BY 
              "CitID" 
            UNION ALL 
            SELECT -- Alternate Name Fact Citations
              CASE 
                WHEN SourceTable.TemplateID = 0 THEN 'Free Form' 
                WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE 
                ELSE 'ERROR: source template does not exist' 
              END AS 'Template' , 
              SourceTable.TemplateID AS 'TpltID' , 
              SourceTable.SourceID AS 'SrcID' , 
              SourceTable.Name COLLATE NOCASE AS 'SourceName' , 
              SourceTable.Fields AS 'SrcFields' , 
              CitationTable.CitationID AS 'CitID' , 
              CitationTable.Fields AS 'CitFields' , 
              CitationTable.Quality AS 'Quality' , 
              CitationTable.ActualText AS 'Citation Text' , 
              CitationTable.Comments AS 'Citation Comment' , 
              'Fact - Alt Name' AS 'Citation Type' , 
              'Alt Name' AS 'FactType' , 
              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' , 
              COUNT( 1 ) AS Records 
            FROM 
              CitationTable LEFT JOIN 
              SourceTable USING 
                ( 
                  SourceID 
                ) LEFT JOIN 
              SourceTemplateTable USING 
                ( 
                  TemplateID 
                ) 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 
            GROUP BY 
              "CitID" -- end of the core UNION of 7 SELECT queries to collect all citations
-- END OF Citations Query                                                                    
          ) 
        UNION ALL 
        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 
        UNION ALL 
        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 
      ) USING 
        ( 
          OwnerID , 
          OwnerType 
        ) 
  ) 
ORDER BY 
  MediaFile , 
  Owner ;