-- This is a simple little query to find Source names in RM which are not
-- associated with any media file. RM calls the names "Master Source names" rather
-- than "Source names", but this RM naming convention is in error in my opinion.
-- There are Places and Place Details, and similarly there should be Sources
-- and Source Details.

-- This query is a part of a project to change all my sources so that all source 
-- information is contained in the RM Source area rather than any of it being 
-- contained in the RM Source Details area. I have a goal that 100% of my Sources
-- will be associated with a supporting media file, and this query will identify 
-- any Source that does not have such a media file.

-- In a perfect world, I would wish to distribute source information appropriately 
-- between the RM Source area and the RM Source Details area. But in my opinion, 
-- the RM tools to manage sources are so woefully deficient that the only rational
-- approach seems to be to place all the source information within the Source area.

-- It may turn out to be a stretch to find an appropriate media file for every single
-- Source, but my definition of media file is quite liberal. Any computer file will do,
-- and the file doesn't necessarily have to be an image file. For example, for a 
-- phone interview the perfect media might be an MP3 recording of the interview. 
-- Generally speaking, I don't have such recordings, but I do have notes that I make
-- from interviews. Those notes can serve as the media for the source.

-- This query is basically just a JOIN between the SourceTable and the MediaLinkTable
-- to identify Sources without media. OwnerType=3 in the MediaLinkTable means the 
-- media is associated with a Source. S.Name < '0' is an easy way to display only 
-- those Source names starting with "*". This is important because all the Source names 
-- I have created since I embarked on this project begin with "*" to distinguish them 
-- from Source names I created before I embarked on this project. Anybody else that 
-- runs this query would probably want to remove ths condition from the query.

-- As a littled added bonus, this query also does a JOIN between the MediaLinkTable
-- and the MultiMediaTable so it can display the MediaType, MediaPath, and MediaFile
-- for each file. My plan is to enter each item into RM as a file rather than as media
-- so that an external viewer will be used for each file rather than the built-in RM
-- viewer. Therefore, each file should have MediaType=2 in the MultiMediaTable and any
-- file for which MediaType=1 reflects a data entry error on my part which I should fix.

-- My copy of SQLiteSpy is set up so that the COLLATE NOCASE is not really required to
-- deal with the RMNOCASE problem. But I included the COLLATE NOCASE so that the query
-- will run ok even if you haven't done anything special to deal with RMNOCASE. The
-- COLLATE NOCASE is embedded in a subquery and hence it is necessary to specify the
-- COLLATE NOCASE only the one time for each data element that has the RMNOCASE problem.


SELECT S.SourceID, S.Name, M.MediaType, M.MediaFile, M.MediaPath

   FROM (SELECT SS.SourceID, SS.Name COLLATE NOCASE FROM SourceTable AS SS) AS S       

             LEFT JOIN

        (SELECT ML.Ownertype, ML.OwnerID, MM.MediaType, MM.MediaFile COLLATE NOCASE, MM.MediaPath
       
            FROM MediaLinkTable AS ML
                     LEFT JOIN
                 MultiMediaTable AS MM
           
            ON ML.MediaId=MM.MediaID
            WHERE ML.OwnerType=3) AS M
           
   ON M.Ownerid = S.SourceID  
                
   WHERE S.Name < '0'    -- only those Source names that begin with "*"
   ORDER BY S.Name, M.MediaType