Happy New Year! I'm trying to write a query that fetches the preferred email, phone, and address for persons from the person authority, but I can't figure out the necessary join(s) I need to get a person's email (emailgroup.email) from a person persontermgroup record. I would have thought it would be a simple hierarchy.parentid join to persontermgroup.id, but it seems it isn't that straightforward. <https://pahma.cspace.berkeley.edu/cspace-authorities/persons/643566c5-b703-409b-bfc2> Thanks in advance, Michael -- Michael Black, Ph.D. Head of Research and Information Phoebe A. Hearst Museum of Anthropology University of California, Berkeley mtblack@berkeley.edu <https://hearstmuseum.berkeley.edu/>

Hi Michael, I think the missing piece is the contacts_common table which has an inauthority field with the authority csid, an initem field with the csid of the term where the email appears, and display name which is the email address. Join contacts_common on contacts_common.id = hierarchy.parentid of emailgroup and that should get you there. Hope that helps! Nathan On Thu, Jan 2, 2020 at 4:29 PM Michael T. Black <mtblack@berkeley.edu> wrote: > Happy New Year! > > I'm trying to write a query that fetches the preferred email, phone, and > address for persons from the person authority, but I can't figure out the > necessary join(s) I need to get a person's email (emailgroup.email) from a > person persontermgroup record. > > I would have thought it would be a simple hierarchy.parentid join to > persontermgroup.id, but it seems it isn't that straightforward. > > <https://pahma.cspace.berkeley.edu/cspace-authorities/persons/643566c5-b703-409b-bfc2> > Thanks in advance, > > Michael > -- > Michael Black, Ph.D. > Head of Research and Information > Phoebe A. Hearst Museum of Anthropology > University of California, Berkeley > mtblack@berkeley.edu > <https://hearstmuseum.berkeley.edu/> > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >

Nathan, Thank you! That did the trick. Much appreciated, Michael On Thu, Jan 2, 2020 at 9:14 PM Nathan Kerr <nathan.p.kerr@gmail.com> wrote: > Hi Michael, > > I think the missing piece is the contacts_common table which has an > inauthority field with the authority csid, an initem field with the csid of > the term where the email appears, and display name which is the email > address. Join contacts_common on contacts_common.id = hierarchy.parentid > of emailgroup and that should get you there. > > Hope that helps! > > Nathan > > On Thu, Jan 2, 2020 at 4:29 PM Michael T. Black <mtblack@berkeley.edu> > wrote: > >> Happy New Year! >> >> I'm trying to write a query that fetches the preferred email, phone, and >> address for persons from the person authority, but I can't figure out the >> necessary join(s) I need to get a person's email (emailgroup.email) from a >> person persontermgroup record. >> >> I would have thought it would be a simple hierarchy.parentid join to >> persontermgroup.id, but it seems it isn't that straightforward. >> >> <https://pahma.cspace.berkeley.edu/cspace-authorities/persons/643566c5-b703-409b-bfc2> >> Thanks in advance, >> >> Michael >> -- >> Michael Black, Ph.D. >> Head of Research and Information >> Phoebe A. Hearst Museum of Anthropology >> University of California, Berkeley >> mtblack@berkeley.edu >> <https://hearstmuseum.berkeley.edu/> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> > -- Michael Black, Ph.D. Head of Research and Information Phoebe A. Hearst Museum of Anthropology University of California, Berkeley mtblack@berkeley.edu <https://hearstmuseum.berkeley.edu/>