12 Publication Related Tables

12.1 `publicationauthors`

This table lists authors as their names are given in publications. Only the initials are stored for authors’ given names. The ContactID links to the author’s full name and contact data in the contacts table. Thus, for a bibliographic entry, Charles Robert Darwin is listed as C. R. Darwin, or as C. Darwin if the publication did not include his middle name. Book editors are also stored in this table if the entire book is cited. However, if a book chapter or section is cited, authors are stored in this table, but the book editors are stored in the publicationeditors table. Thus, for the following reference, G. C. Frison is stored in the publicationauthors table.

Frison, G. C., editor. 1996. The Mill Iron site. University of New Mexico Press, Albuquerque, New Mexico, USA.

Whereas for the following publication, L. S. Cummings is listed in the PublicationAuthors table, and G. C. Frison is listed in the publicationeditors table.

Cummings, L. S. 1996. Paleoenvironmental interpretations for the Mill Iron site: stratigraphic pollen and phyrolith analysis. Pages 177-193 in G. C. Frison, editor. The Mill Iron site. University of New Mexico Press, Albuquerque, New Mexico, USA.

authorid (Primary Key): An arbitrary Author identification number.
publicationid (foreign key): Publication identification number. Field links to the publications table.
authororder: Ordinal number for the position in which the author's name appears in the publication’s author list.
familyname: Family name of author
initials: Initials of author’s given names
suffix: Authors suffix (e.g. «Jr.»)
contactid (foreign key): Contact identification number. Field links to the contacts table.

12.1.1 SQL Example

The following query lists authors of publications from the province of Ontario, and provides the publicationids as an array, along with a total count of the publications they have authored about sites in the province:

SELECT ct.contactname, array_agg(DISTINCT pa.publicationid) AS publications, COUNT(DISTINCT pa.publicationid) AS pubcount
FROM         ndb.geopoliticalunits AS gpu
INNER JOIN    ndb.sitegeopolitical AS sgp ON  sgp.geopoliticalid = gpu.geopoliticalid
INNER JOIN     ndb.collectionunits AS  cu ON           cu.siteid = sgp.siteid
INNER JOIN            ndb.datasets AS  ds ON ds.collectionunitid = cu.collectionunitid
INNER JOIN ndb.datasetpublications AS dsp ON       dsp.datasetid = ds.datasetid
INNER JOIN  ndb.publicationauthors AS  pa ON    pa.publicationid = dsp.publicationid
INNER JOIN            ndb.contacts AS  ct ON        ct.contactid = pa.contactid
WHERE gpu.geopoliticalname = 'Ontario' 
  AND gpu.rank = 2
GROUP BY ct.contactname
ORDER BY COUNT(DISTINCT pa.publicationid) DESC
LIMIT 10;

Table 12.1: Displaying records 1 - 10
contactname	publications	pubcount
McAndrews, John H.	{69,139,140,206,268,272,274,276,278,279,280,284,285,287,290,291,292,438,459,572,748,6101,7732,7775,8111,8112,8148,8149,8167,8208,13956,15736}	32
Morgan, Alan V.	{272,281,459,6086,6090,6091,6101,6125,7654,7724,7927,7928,8270,10014,10257,11070,11590,14132}	18
Finkelstein, Sarah A.	{7511,7512,7839,9878,10675,16076,16077,16089,16665,16666,16681,16682,16964,18393,19328,19482}	16
Fox, William Anthony	{10779,10794,10878,10959,10996,10997,11040,11284,11534,11535,12786,14022,14023,14024,14087,14116}	16
Smol, John P.	{9695,9751,9838,9839,9841,9844,9846,9847,9848,9849,9850,9852,9946,19478}	14
Karrow, Paul F.	{6086,6101,7723,7724,7732,7733,8353,11070,11142,11143,11590}	11
Dixit, Aruna S.	{9838,9839,9841,9844,9846,9847,9848,9849,9850,9852}	10
Dixit, Sushil S.	{9838,9839,9841,9844,9846,9847,9848,9849,9850,9852}	10
Jackson, Lawrence J.	{280,573,11096,11171,11282,12691,13956,14051,14052,14053}	10
Churcher, C.S. (Rufus)	{1640,7928,10247,11142,11143,11443,11585,14132}	8

12.2 `publicationeditors`

This table stores the editors of publications for which chapters or sections are the primary bibliographic entries. Chapter authors are stored in the PublicatonAuthors table, where they are linked to the contacts table. However, publication editors are not cross-referenced in the contacts table, because chapter authors are the principal citation.

editorid (primary key): An arbitrary Editor identification number.
publicationid (foreign key): Publication identification number. Field links to the publications table.
editororder: Ordinal number for the position in which the editor’s name appears the publication’s author list.
familyname: Family name of editor
initials: Initials of editor’s given names
suffix: Authors suffix (e.g. «Jr.»)

12.3 `publications`

This table stores publication or bibliographic data. The table is designed with fields for bibliographic data so that bibliographies can be formatted in different styles and potentially exported to bibliographic software such EndNote®. In the constituent databases that were originally merged into Neotoma, bibliographic entries were not parsed into separate fields, but rather were stored as free-form text.

Because complete parsing of these thousands of legacy bibliographic entries into individual fields would have been prohibitively time consuming, the existing bibliographic data were ingested “as is” with a PubTypeID = Other. However, for legacy publications, the year of publication was added to the Year field, and authors were parsed into the publicationauthors table and added to the contacts table. In addition, some global changes were made. For example, «Pp.» was changed to «Pages», «Ed.» to «Editor», and «Eds.» to «Editors». Also for FAUNMAP entries, abbreviated journal names were changed to fully spelled out names.

The merged databases used different bibliographic styles, and data entry personnel working on the same database sometimes followed different conventions. Consequently, the current bibliographic entries are not stylistically uniform. Eventually, the legacy bibliographic data will be parsed into separate fields.

The Publications table has fields to accommodate a number of different types of publications. Some fields contain different kinds of data for different kinds of publications. For example, the BookTitle field stores the titles of books, but stores the journal name for journal articles. The Publisher field stores the name of the publisher for books, but the name of the university for theses and dissertations.

Authors are stored in the publicationauthors table. Editors are also stored in the publicationauthors table if the entire publication is cited. The publicationauthors table has a ContactID field, which links to the contacts table, where full names and contact information is stored for authors and editors. The PubTypeID «Authored Book» or «Edited Book» indicates whether the PublicationAuathors records are authors or editors. If a book chapter or section is the primary bibliographic entry, then the book editors are stored in the publicationeditors table, which does not have a ContactID field.

publicationid (primary key): An arbitrary Publication identification number.
pubtypeid (foreign key): Publication type. Field links to the publicationtypes lookup table.
year: Year of publication.
citation: The complete citation in a standard style. For Legacy citations inherited from other databases, this field holds the citation as ingested from the other databases.
articletitle: The title of a journal or book chapter article.
booktitle: The title of a book or journal
volume: The volume number of a journal or the volume number of a book in a set. A set of books is comprised of a fixed number of volumes and normally have ISBN numbers, not ISSN numbers. Book sets are often published simultaneously, but not necessarily. For instance, many floras, such as The Flora of North America north of and Flora Europaea, consist of a set number of volumes planned in advance but published over a period of years.
issue: Journal issue number, normally included only if issues are independently paginated.
pages: Page numbers for journal or book chapter articles, or the number of pages in theses, dissertations, and reports.
citationnumber: A citation or article number used in lieu of page numbers fordigital or online publications, typically used in conjunction with the DOI. For example, journals published by the American Geophysical Union since 1999 use citation numbers rather than page numbers.
doi: Digital Object Identifier. A unique identifier assigned to digital publications. The DOI consists of a prefix and suffix separated by a slash. The portion before the slash stands for the publisher and is assigned by the International DOI Foundation. For example, 10.1029 is the prefix for the American Geophysical Union. The suffix is assigned by the publisher according to their protocols. For example, the DOI 10.1029/2002PA000768 is for an article submitted to Paleoceanography in 2002 and is article number 768 submitted since the system was installed. An example of CitationNumber and DOI:

Barron, J. A., L. Heusser, T. Herbert, and M. Lyle. 2003. High-resolution climatic evolution of coastal northern during the past 16,000 years, Paleoceanography 18(1):1020. DOI:10.1029/2002PA000768.
numvolumes: Number of volumes in a set of books. Used when the entire set is referenced. An example of NumVolumes and Edition:

Wilson, D. E., and D. M. Reeder. 2005. Mammal species of the world: a taxonomic and geographic reference. Third edition. 2 volumes. The Johns Hopkins University Press, Baltimore, Maryland, USA.
edition: Edition of a publication.
volumetitle: Title of a book volume in a set. Used if the individual volume is referenced. Example of Volume and VolumeTitle:

Flora of North America Editorial Committee. 2002. Flora of North America north of . Volume 26. Magnoliophyta: Liliidae: Liliales and Orchidales. Oxford University Press, New York, New York, USA.
seriestitle: Title of a book series. Book series consist of a series of books, typically published at irregular intervals on sometimes related but different topics. The number of volumes in a series is typically open ended. Book series are often assigned ISSN numbers as well as ISBN numbers. However, in contrast to most serials, book series have individual titles and authors or editors. Citation practices for book series vary; sometimes they are cited as books, other times as journals. The default citation for Neotoma includes all information. An example of SeriesTitle and SeriesVolume:

Curtis, J. H., and D. A. Hodell. 1993. An isotopic and trace element study of ostracods from , : A 10,500 year record of paleosalinity and paleotemperature changes in the . Pages 135-152 in P. K. Swart, K. C. Lohmann, J. McKensie, and S. Savin, editors. Climate change in continental isotopic records. Geophysical Monograph 78. American Geophysical Union, Washington, D.C., USA.

seriesvolume: Volume number in a series.
publisher: Publisher, including commercial publishing houses, university presses, government agencies, and non-governmental organizations, generally the owner of the copyright.
city: City in which the publication was published. The first city if a list is given.
state: State or province in which the publication was published. Used for the and , not used for many countries.
country: Country in which the publication was published, generally the complete country name, but «» for the .
originallanguage: The original language if the publication or bibliographic citation is translated from another language or transliterated from a non-Latin character set. Field not needed for non-translated publications in languages using the Latin character set. In the following example, the ArticleTitle is translated from Russian to English and the BookTitle (journal name) is transliterated from Russian:

Tarasov, P.E. 1991. Late Holocene features of the Kokchetav Highland. Vestnik Moskovskogo Universiteta. Series 5. Geography 6:54-60 \[in **Russian**\].
notes: Free form notes or comments about the publication, which may be added parenthetically to the citation.

12.4 `publicationtypes`

Lookup table of publication types. This table is referenced by the publications table.

pubtypeid: An arbitrary Publication Type identification number.
pubtype: Publication Type. The database has the following types:
- Authored Book: An authored book
- Edited Book: An edited book
- Master’s Thesis: A Master’s thesis
- Doctoral Dissertation: A doctoral dissertation or Ph.D. thesis
- Authored Report: An authored report
- Edited Report: An edited report
- Other Authored: An authored publication not fitting in any other category (e.g. web sites, maps)
- Other Edited: A edited publication not fitting into any other category

Examples of the different Publication Types are given in the following sections. Shown for each Publication Type are the fields in the publications table that may be filled for that type, with the exception that originallanguage and notes are not shown unless used.

12.4.1 Legacy

Legacy citation ingested from another database and not parsed into separate fields:

SELECT pub.publicationid, ARRAY_AGG(ct.contactname), pub.year, pub.citation
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Legacy'
GROUP BY pub.publicationid
LIMIT 1;

Table 12.2: 1 records
publicationid	array_agg	year	citation
98	{“Mott, Robert J.”}	1976	Mott, R.J. 1976. A Holocene pollen profile from the Sept-Iles area, Québec. Naturaliste Canadien 103:457-467.

12.4.2 Journal Article

Articles published in a journal.

SELECT pub.publicationid, 
       ARRAY_AGG(ct.contactname),
       pub.year,
       pub.articletitle,
       pub.journal,
       pub.volume,
       pub.issue,
       pub.pages,
       pub.doi
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Journal Article'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.3: 3 records
publicationid	array_agg	year	articletitle	journal	volume	issue	pages	doi
4	{“Whitlock, Cathy”,“Villarosa, Gustavo”,“Nanavati, William, P.”,“Outes, Valeria”}	2011	Diatom-inferred wind activity at Lac du Sommet, southern Québec, Canada: A multiproxy paleoclimate reconstruction based on diatoms, chironomids, and pollen for the past 9500 years	The Holocene	21	6	925-938	10.1177/0959683611400199
5	{“Janssen, C. Roel”}	1968	Myrtle Lake: a late- and post-glacial pollen diagram from northern Minnesota	Canadian Journal of Botany	46	11	1397-1410	10.1139/b68-190
6	{“Sirkin, Leslie A.”,“Denny, Charles S.”,“Denny, Charles S.”,“Sirkin, Leslie A.”,“Denny, Charles S.”,“Rubin, Meyer”}	1977	Late Pleistocene environment of the central Delmarva Peninsula, Delaware-Maryland	Geological Society of America Bulletin	88	NA	139-142	10.1130/0016-7606(1977)88<139:lpeotc>2.0.co;2

12.4.3 Book Chapter

Chapter or section in an edited book.

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.articletitle,
       ARRAY_AGG(pe.initials || ', ' || pe.familyname) AS editors,
       pub.volume,
       pub.pages,
       pub.booktitle,
       pub.edition,
       pub.volumetitle,
       pub.seriestitle,
       pub.seriesvolume,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.publicationeditors AS pe ON pe.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Book Chapter'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.4: 3 records
publicationid	authors	year	articletitle	editors	volume	pages	booktitle	edition	volumetitle	seriestitle	seriesvolume	publisher	city	state	country
3	{“Davis, Margaret B.”}	1978	Climatic interpretation of pollen in Quaternary sediments	{“D., Walker”,“J.C., Guppy”}	NA	35-51	Biology and Quaternary environments	NA	NA	NA	NA	Australian Academy of Science	Canberra City	NA	Australia
7	{“Maher, Louis J., Jr.”}	1982	The palynology of Devils Lake, Sauk County, Wisconsin	{“J.C., Knox”,“L., Clayton”,“D.M., Mickelson”}	NA	119-135	Quaternary History of the Driftless Area	NA	NA	Field Trip Guide Book	5	University of Wisconsin-Extension, Geological and Natural History Survey	Madison	Wisconsin	USA
23	{“Whitehead, Donald R.”}	1967	Studies of full-glacial vegetation and climate in southeastern United States	{“E.J., Cushing”,“H.E., Wright”}	NA	237-248	Quaternary Paleoecology	NA	NA	NA	NA	Yale University Press	New Haven	Connecticut	USA

12.4.4 Authored Book

A book published by a single, or multiple authors.

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.volume,
       pub.booktitle,
       pub.edition,
       pub.numvolumes,
       pub.volumetitle,
       pub.seriestitle,
       pub.seriesvolume,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Authored Book'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.5: 3 records
publicationid	authors	year	volume	booktitle	edition	numvolumes	volumetitle	seriestitle	seriesvolume	publisher	city	state	country
73	{“Camfield, M.”,“Mott, Robert J.”}	1969	NA	Palynological studies in the Ottawa area	NA	NA	NA	Paper	69-38	Geological Survey of Canada	Ottawa	NA	Canada
74	{“Mott, Robert J.”}	1973	NA	Palynological studies in central Saskatchewan: pollen stratigraphy from lake sediment sequences	NA	NA	NA	Paper	72-49	Geological Survey of Canada	Ottawa	Ontario	Canada
92	{“Jones, Alice Simms”}	1974	NA	Late-glacial—postglacial vegetational history of the Pretty Lake region, northeastern Indiana	NA	NA	NA	U.S. Geological Survey Professional Paper	686-B	U. S. Government Printing Office	Washington DC	NA	USA

12.4.5 Edited Book

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       ARRAY_AGG(DISTINCT pe.initials || ', ' || pe.familyname) AS editors,
       pub.year,
       pub.volume,
       pub.booktitle,
       pub.edition,
       pub.numvolumes,
       pub.volumetitle,
       pub.seriestitle,
       pub.seriesvolume,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
LEFT OUTER JOIN ndb.publicationeditors AS pe ON pe.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Edited Book'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.6: 3 records
publicationid	authors	editors	year	volume	booktitle	edition	numvolumes	volumetitle	seriestitle	seriesvolume	publisher	city	state	country
67	{“Albert, Lois E.”,“Wyckoff, Don G.”}	{NULL}	1981	NA	Ferndale Bog and Natural Lake: five thousand years of environmental change in southeastern Oklahoma	NA	NA	NA	Studies in Oklahoma’s Past	7	Oklahoma Archaeological Survey	Norman	Oklahoma	USA
299	{“Flora of North America Editorial Committee”}	{NULL}	1993	2	Flora of North America north of Mexico	NA	NA	Pteridophytes and Gymnosperms	NA	NA	Oxford University Press	New York	New York	USA
613	{“Flora of North America Editorial Committee”}	{NULL}	1997	3	Flora of North America north of Mexico	NA	NA	Magnoliophyta: Magnoliidae and Hamamelidae	NA	NA	Oxford University Press	New York	New York	USA

12.4.6 Master’s Thesis

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.pages,
       pub.articletitle,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Master''s Thesis'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.7: 3 records
publicationid	authors	year	pages	articletitle	publisher	city	state	country
44	{“Stravers, Loreen K. Stegeman”}	1981	NA	Palynology and deglaciation history of the central Labrador-Ungava Peninsula	University of Colorado	Boulder	Colorado	USA
115	{“Anderson, R. Scott”}	1979	NA	A Holocene record of vegetation and fire at Upper South Branch Pond in northern Maine	University of Maine	Orono	Maine	USA
163	{“Smith, Everett Newman, Jr.”}	1984	NA	Late-Quaternary vegetational history at Cupola Pond, Ozark National Scenic Riverways, southeastern Missouri	University of Tennessee	Knoxville	Tennessee	USA

12.4.7 Doctoral Dissertation

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.pages,
       pub.articletitle,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Master''s Thesis'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.8: 3 records
publicationid	authors	year	pages	articletitle	publisher	city	state	country
44	{“Stravers, Loreen K. Stegeman”}	1981	NA	Palynology and deglaciation history of the central Labrador-Ungava Peninsula	University of Colorado	Boulder	Colorado	USA
115	{“Anderson, R. Scott”}	1979	NA	A Holocene record of vegetation and fire at Upper South Branch Pond in northern Maine	University of Maine	Orono	Maine	USA
163	{“Smith, Everett Newman, Jr.”}	1984	NA	Late-Quaternary vegetational history at Cupola Pond, Ozark National Scenic Riverways, southeastern Missouri	University of Tennessee	Knoxville	Tennessee	USA

12.4.8 Authored Report

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.pages,
       pub.articletitle,
       pub.seriestitle,
       pub.seriesvolume,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Authored Report'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.9: 3 records
publicationid	authors	year	pages	articletitle	seriestitle	seriesvolume	publisher	city	state	country
1	{“International Organization for Standardization”}	2006	NA	Codes for the representation of names of countries and their subdivisions — Part 1: Country codes	ISO	3166-1	NA	Geneva	NA	Switzerland
2	{“United States Central Intelligence Agency”}	2004	NA	CIA World Factbook	NA	NA	NA	Washington, DC	NA	USA
66	{“Miller, Norton G.”}	1973	NA	Late-glacial and postglacial vegetation change in southwestern New York State	Bulletin	420	New York State Museum and Science Service, The State Education Department	Albany	New York	USA

12.4.9 Edited Report

SELECT pub.publicationid, 
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       ARRAY_AGG(DISTINCT pe.initials || ', ' || pe.familyname) AS editors,
       pub.year,
       pub.pages,
       pub.articletitle,
       pub.seriestitle,
       pub.seriesvolume,
       pub.publisher,
       pub.city,
       pub.state,
       pub.country
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
LEFT OUTER JOIN ndb.publicationeditors AS pe ON pe.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Edited Report'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.10: 3 records
publicationid	authors	editors	year	pages	articletitle	seriestitle	seriesvolume	publisher	city	state	country
394	{“Gajewski, Konrad J.”}	{NULL}	1985	NA	Late-Holocene pollen data from lakes with varved sediments in northeastern and northcentral United States	IES Report	124	Center for Climatic Research, University of Wisconsin	Madison	Wisconsin	USA
7290	{“Saltonstall, Patrick G.”,“Steffian, Amy F.”}	{NULL}	2004	NA	Settlements of the Ayakulik-Red River Drainage, Kodiak Archipelago, Alaska: Comprehensive Project Report, 2001-2004	NA	NA	Alutiiq Museum and Archaeological Repository	Kodiak	Alaska	United States
8285	{“Bischoff, James L.”,“Smith, George I.”}	{NULL}	1993	NA	Core OL-92 from Owens Lake, Southeast California	Open-File Report	93-683	U.S. Geological Survey	Denver	Colorado	NA

12.4.10 Other Authored Publication

SELECT pub.publicationid,
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.pages,
       pub.articletitle
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Other Authored'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.11: 3 records
publicationid	authors	year	pages	articletitle
275	{“Calkin, Parker E.”,“McAndrews, John H.”}	1980	NA	Geology and paleontology of two late Wisconsin sites in western New York State. Geological Society of America Bulletin 91:295-306.
1045	{“Blyakharchuk, Tatyana A.”}	1988	NA	Sopryazhenost’ sporovo-pyl’zevogo i botanicheskogo analisov torfa pri izuchenii leso-bolotnykh landshaftov yuga Zapadnoi Sibiri [Palynological and botanical analyses of peat in the investigation of forest-mire landscapes of the south West Siberia]. Pages 16-18 in Problemy Golocena, Tbilisi.
1054	{“Bottema, Sytze”,“van Zeist, Wim”}	1981	NA	Palynological evidence for the climatic history of the near East: 50,000-6000 BP. Pages 111-132 in Préhistoire du Levant. Colloques Internationaux du CNRS, 598. Editions CNRS

12.4.11 Other Edited Publication

SELECT pub.publicationid,
       ARRAY_AGG(DISTINCT ct.contactname) AS authors,
       pub.year,
       pub.pages,
       pub.articletitle
FROM ndb.publications AS pub
INNER JOIN ndb.publicationtypes AS pt ON pt.pubtypeid = pub.pubtypeid 
INNER JOIN ndb.publicationauthors AS pa ON pa.publicationid = pub.publicationid
INNER JOIN ndb.contacts as ct ON ct.contactid = pa.contactid
WHERE pt.pubtype = 'Other Edited'
GROUP BY pub.publicationid
LIMIT 3;

Table 12.12: 3 records
publicationid	authors	year	pages	articletitle
1230	{“Mariscal Alvarez, Blanca”}	1989	NA	Comparacion palinologica entre una turbera de la cordillera central y unas turberas de la cordillera cantabrica
11433	{“Matthews, John V., Jr.”,“Morlan, Richard E.”,“Sandhu, A.”,“Schweger, Charles E.”,“Westgate, John A.”}	1995	NA	Tephrochronological, palaeomagnetic and palaeoenvironmental studies of Late Cenozoic deposits in the northern Yukon, Canada
15893	{“de Beaulieu, Jacques Louis”}	1978	NA	Analyses polliniques dans les Alpes méridionales françaises