How do you list sources without media?

[derieppe]

Hello,

To check the quality of the information, I would like to check if some of my sources are without attached media.

I can't find a report for this on the TNG Wiki. Would you have a solution? 

Thank you in advance.

Sincerely

[GOGGS]

This will find and list Sources that do not have a medialink (if you paste this into a Report using SQL): 

SELECT l.gedcom, s.sourceID, l.linktype, l.personID  
FROM tng_sources s  
LEFT JOIN tng_medialinks l  
ON s.sourceID = l.personID 
AND s.gedcom = l.gedcom 
WHERE ISNULL(l.personID) = 1

If you want to see all of the sources (and ones without a medialink would show NULL vs. others with values for the other 3 fields), just remove the WHERE clause (the whole last line).

If you have "broken" medialinks, we could add another table to make sure the links have a media file that they point to:

SELECT l.gedcom, s.sourceID, l.linktype, l.personID,
mediakey, path, thumbpath 
FROM tng_sources s  
LEFT JOIN tng_medialinks l  
ON s.sourceID = l.personID AND s.gedcom = l.gedcom 
LEFT JOIN tng_media m 
ON l.mediaID = m.mediaID AND l.gedcom = m.gedcom

 

GOGGS

[derieppe]

Hi GOGGS,

Thanks a lot for your quick answer.

• Solution 1 give me a syntax error : "Unknown column 'tng_people.personID' in 'SELECT'" . I use TNG v13.1.2
• Syntax 2 give me a list of sources with the source ID in a "ID Personne"  column. Some sources seems to have no tree linked... Strange ! I will analyse results and come back to you soon.

Regards

[GOGGS]

Sounds good...

Your error implies that the tng_people table is in the query; it should not be. See my screenshot...

 

GOGGS

[derieppe]

Hi GOGGS,

Thanks for your answer. I think problem is in the TNG version or a maintenance patch i forget to execute : this is the RUN message i get :

SQL: SELECT l.gedcom, s.sourceID, l.linktype, l.personID FROM tng_sources s LEFT JOIN tng_medialinks l ON s.sourceID = l.personID AND s.gedcom = l.gedcom WHERE ISNULL(l.personID) = 1

Une erreur est intervenue dans le logiciel TNG. Ce que vous devez faire :
Si vous venez d'installer une mise à jour, vous avez peut-être sauté une partie des instructions d'installation. Retournez à la page du Lisez-moi pour relire les instructions. Prêtez une attention particulière à l'étape de la structure de la base de données.

Si vous venez de faire une première installation de TNG, il est peut-être encore nécessaire de créer les tables de la base de données. Revenez à la page du lisez-moi pour retrouver cette étape.

Si vous êtes le propriétaire du site, vous pouvez contacter le service assistance de TNG pour obtenir une aide supplémentaire sur ce problème. Merci de copier la requête ci-après et collez-la dans votre message.

Requête: SELECT count( tng_people.personID ) as rcount FROM tng_sources s LEFT JOIN tng_medialinks l ON s.sourceID = l.personID AND s.gedcom = l.gedcom WHERE ISNULL(l.personID) = 1

Unknown column 'tng_people.personID' in 'SELECT'

The COUNT(tng_people.personID) is not in the SQL requete. But it appears in the reply to the request...

Regards

[GOGGS]

You should just paste the first SQL statement in a new Report (SQL only) and run that...

 

GOGGS

[derieppe]

Hi,

Done but i get same result (SQL error).

I will try on a "fresh" installation to check... 

Regards