-- MediaDeleteUnused.sql
/* 
2013-01-21 Tom Holden ve3meo

Deletes records from MultiMediaTable untagged by 
any Person, Family, Fact, Place, Source or Citation.

Extracted from DeletePhantoms.sql
*/
DELETE
FROM MultimediaTable
WHERE MediaID NOT IN (
		-- Person media
		SELECT MediaID
		FROM MediaLinkTable
		WHERE OwnerType = 0
			AND OwnerID IN (
				SELECT PersonID
				FROM PersonTable
				)
		
		UNION
		
		-- Family media
		SELECT MediaID
		FROM MediaLinkTable
		WHERE OwnerType = 1
			AND OwnerID IN (
				SELECT FamilyID
				FROM FamilyTable
				)
		
		UNION
		
		-- Event Media
		SELECT MediaID
		FROM MediaLinkTable
		WHERE OwnerType = 2
			AND OwnerID IN (
				SELECT EventID
				FROM EventTable
				)
		
		UNION
		
		-- Master Source Media
		SELECT MediaID
		FROM MediaLinkTable
		WHERE OwnerType = 3
			AND OwnerID IN (
				SELECT SourceID
				FROM SourceTable
				)
		
		UNION
		
		-- Citation Media
		SELECT MediaID
		FROM MediaLinkTable
		WHERE OwnerType = 4
			AND OwnerID IN (
				SELECT CitationID
				FROM CitationTable
				)
		
		UNION
		
		-- Place Media
		SELECT MediaID
		FROM MediaLinkTable
		WHERE OwnerType = 5
			AND OwnerID IN (
				SELECT PlaceID
				FROM PlaceTable
				)
		);