Before asking for help please read "How to request help" by clicking on that tab above here.
  • Page:
  • 1

TOPIC: Help with SQL Query individuals and media

Help with SQL Query individuals and media 1 week 6 days ago #1

  • gerag
  • gerag's Avatar Topic Author
  • Offline
  • New
  • New
  • Posts: 3
I hope someone can help me with this one.
I am looking for a way to export a list of individuals and their connected media and corresponding media files. (without recurring to the gedcom file)
I tried to do this with an SQL Query to the corresponding tables: individuals, media and media_files. Yet I found no way to connect those tables. I guess the tables are linked through some other table, yet so far have not found any that would qualify. Yet such a link must exist,otherwise it would not be possible to show the corrent picture for each individual.

Can anyone help? It would be very much appreciated.

Please Log in or Create an account to join the conversation.

Help with SQL Query individuals and media 1 week 6 days ago #2

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 12929
> I guess the tables are linked through some other table, yet so far have not found any that would qualify

The table is called "link" ;-)

Try this

individuals -< links -< media -< media files
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

Help with SQL Query individuals and media 1 week 6 days ago #3

  • gerag
  • gerag's Avatar Topic Author
  • Offline
  • New
  • New
  • Posts: 3
Thank you very much! At first glance link didn't seem the right but after inspecting it a bit deeper I found it out.
If anyone needs it in the future here is my query:

SELECT i_id, media.m_id, descriptive_title, n_full FROM individuals
inner join link on l_from = i_id and l_type = 'OBJE'
inner join media on link.l_to = media.m_id
inner join media_file on media.m_id = media_file.m_id
left join name on individuals.i_id = name.n_id
where i_file = 8 (Gedcom file number)
and n_type = 'NAME'


At least this gives a basis to work upon.

Please Log in or Create an account to join the conversation.

Last edit: by gerag.
  • Page:
  • 1
Powered by Kunena Forum