-- MediaTags-DeletePersonalHavingFactDupes.sql
/*
2012-12-28 Tom Holden ve3meo

Deletes Personal MediaTags (Person-General) for media
having duplicate tags to an event for the same person.
*/ 

-- SELECT LinkID, MediaID, OwnerID -- testing 
DELETE
FROM MediaLinkTable 
WHERE OwnerType=0 
AND MediaID || '.' || OwnerID
IN
(
-- build ordered list of event mediatags in form MediaID.PersonID
SELECT DISTINCT MediaID || '.' || PersonID AS MediaPerson
FROM
(
-- LinkIDs for PersonID's having Indiv Event MediaTags
SELECT DISTINCT LinkID, MediaID, E.OwnerID AS PersonID FROM MediaLinkTable ML
INNER JOIN EventTable E
ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 0

UNION

-- LinkIDs for Husband PersonID's having Family Event MediaTags
SELECT DISTINCT LinkID, MediaID, F.FatherID AS HusbandID FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 1
INNER JOIN FamilyTable F
 ON E.OwnerID = F.FamilyID

UNION

-- LinkIDs for Wife PersonID's having Family Event MediaTags
SELECT DISTINCT LinkID, MediaID, F.MotherID AS WifeID 
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 1
INNER JOIN FamilyTable F
 ON E.OwnerID = F.FamilyID
)
ORDER BY MediaPerson
);