Report: People *with* associated media, but *without* default photo

Description: People *with* associated media, but *without* default photo

This report was greatly improved by Warren S Gilbert. This report shows people with no default photos. If you want to include other categories, then delete the statements like: [AND m1.mediatypeID != "histories"]

Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate

---

SELECT p1.personID, p1.lastname, p1.firstname, p1.living, p1.gedcom,  m1.description, m1.mediatypeID
FROM tng_media m1
LEFT OUTER JOIN  tng_medialinks m2 ON m1.gedcom = m2.gedcom
AND m1.mediaID =  m2.mediaID
LEFT OUTER JOIN tng_people p1 ON m2.gedcom = p1.gedcom
AND  m2.personID = p1.personID
LEFT OUTER JOIN (

SELECT gedcom, personID,  defphoto
FROM tng_medialinks
WHERE defphoto = "1"
)m3 ON p1.gedcom =  m3.gedcom
AND p1.personID = m3.personID
WHERE m2.medialinkID IS NOT NULL  
AND m1.mediatypeID != "documents"
AND m1.mediatypeID !=  "histories"
AND m1.mediatypeID != "headstones"
AND p1.personID IS NOT NULL  
AND m3.personID IS NULL 
ORDER BY p1.lastname, p1.firstname,  p1.birthdatetr,
firstname, birthdatetr

---