API tools faq
paste
Advertisement
roganhamby

Extend Reporter - Awards

roganhamby
Apr 13th, 2015
281
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 32.75 KB | None | 0 0
raw download clone embed print report
  1. /* a way to do it
  2.  
  3. create table extend_reporter.awards (
  4. award text,
  5. category text,
  6. year integer,
  7. author text,
  8. title text,
  9. notes text
  10. );
  11.  
  12. */
  13.  
  14. select a.year, a.author, a.title, a.notes, ac.category, aa.agency
  15. from extend_reporter.awards a
  16. join extend_reporter.awards_agency aa on aa.id = a.agency
  17. join extend_reporter.awards_category ac on ac.id = a.category
  18. where ac.category = 'Novel' and aa.agency in ('Hugo','Nebula')
  19. order by 6,1;
  20.  
  21.  
  22.  
  23. <class id="erawagency" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="extend_reporter::awards_agency" oils_persist:tablename="extend_reporter.awards_agency" reporter:core="false" reporter:label="Award Agencies">
  24. <fields oils_persist:primary="id">
  25. <field reporter:label="Agency Name" name="agency" reporter:datatype="text"/>
  26. </fields>
  27. </class>
  28.  
  29. <class id="erawcat" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="extend_reporter::awards_category" oils_persist:tablename="extend_reporter.awards_category" reporter:core="false" reporter:label="Award Categories">
  30. <fields oils_persist:primary="id">
  31. <field reporter:label="Category Name" name="category" reporter:datatype="text"/>
  32. </fields>
  33. </class>
  34.  
  35.  
  36. <class id="eraw" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="extend_reporter::awards" oils_persist:tablename="extend_reporter.awards" reporter:core="false" reporter:label="Award Winners">
  37. <fields oils_persist:primary="id">
  38. <field reporter:label="All Award Agencies" name="agency" oils_persist:virtual="true" reporter:datatype="link"/>
  39. <field reporter:label="All Award Categories" name="category" oils_persist:virtual="true" reporter:datatype="link"/>
  40. <field reporter:label="Year of Award" name="year" reporter:datatype="int"/>
  41. <field reporter:label="Author" name="author" reporter:datatype="text"/>
  42. <field reporter:label="Title" name="title" reporter:datatype="text"/>
  43. <field reporter:label="Notes" name="notes" reporter:datatype="text"/>
  44. </fields>
  45. <links>
  46. <link field="agency" reltype="has_a" key="id" map="" class="erawagency"/>
  47. <link field="category" reltype="might_have" key="id" map="" class="erawcat"/>
  48. </links>
  49. </class>
  50.  
  51.  
  52. create table extend_reporter.awards_agency (
  53. id serial PRIMARY KEY,
  54. agency text
  55. );
  56.  
  57. insert into extend_reporter.awards_agency (agency) values ('Hugo');
  58. insert into extend_reporter.awards_agency (agency) values ('Nebula');
  59. insert into extend_reporter.awards_agency (agency) values ('Pultizer');
  60. insert into extend_reporter.awards_agency (agency) values ('Booker');
  61. insert into extend_reporter.awards_agency (agency) values ('Newberry');
  62. insert into extend_reporter.awards_agency (agency) values ('Caldecott');
  63. insert into extend_reporter.awards_agency (agency) values ('Nobel');
  64.  
  65. create table extend_reporter.awards_category (
  66. id serial PRIMARY KEY,
  67. category text
  68. );
  69.  
  70. insert into extend_reporter.awards_category (category) values
  71. ('Fiction');
  72. insert into extend_reporter.awards_category (category) values
  73. ('Novel');
  74. insert into extend_reporter.awards_category (category) values
  75. ('Literature');
  76. insert into extend_reporter.awards_category (category) values
  77. ('Special');
  78.  
  79. create table extend_reporter.awards (
  80. id serial,
  81. agency INT NOT NULL REFERENCES extend_reporter.awards_agency (id),
  82. category INT REFERENCES extend_reporter.awards_category (id),
  83. year integer NOT NULL,
  84. author text NOT NULL,
  85. title text,
  86. notes text
  87. );
  88.  
  89.  
  90. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2014,'Leckie, Ann','Ancillary Justice','');
  91. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2013,'Scalzi, John','Redshirts','');
  92. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2012,'Walton, Jo','Among Others','');
  93. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2011,'Willis, Connie','Blackout/All Clear','');
  94. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2010,'Mieville, China','The City & The City','tie');
  95. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2009,'Gaiman, Neil','The Graveyard Book','');
  96. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2010,'Bacigalupi, Paolo','The Windup Girl','tie');
  97. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2008,'Chabon, Michael','The Yiddish Policeman''s Union','');
  98. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2007,'Vinge, Vernor','Rainbow''s End','');
  99. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2006,'Wilson, Robert Charles','Spin','');
  100. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2005,'Clarke, Susanna','Johathan Strange & Mr. Norrell','');
  101. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2004,'Bujold, Lois McMaster','Paladin of Souls','');
  102. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2003,'Sawyer, Robert J','Hominids','');
  103. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2002,'Gaiman, Neil','American Gods','');
  104. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2001,'Rowling, J.K.','Harry Potter and the Goblet of Fire','');
  105. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,2000,'Vinge, Vernor','A Deepness in the Sky','');
  106. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1999,'Willis, Connie','To Say Nothing of the Dog','');
  107. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1998,'Haldeman, Joe','Forever Peace','');
  108. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1997,'Robinson, Kim Stanley','Blue Mars','');
  109. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1996,'Stephenson, Neal','The Diamond Age','');
  110. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1995,'Bujold, Lois McMaster','Mirror Dance','');
  111. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1994,'Robinson, Kim Stanley','Green Mars','');
  112. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1993,'Vinge, Vernor','A Fire Upon the Deep','tie');
  113. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1993,'Willis, Connie','Doomsday Book','tie');
  114. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1992,'Bujold, Lois McMaster','Barrayar','');
  115. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1991,'Bujold, Lois McMaster','The Vor Game','');
  116. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1990,'Simmons, Dan','Hyperion','');
  117. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1989,'Cherryh, C.J.','Cyteen','');
  118. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1988,'Brin, David','The Uplift War','');
  119. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1987,'Card, Orson Scott','Speaker for the Dead','');
  120. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1986,'Card, Orson Scott','Ender''s Game','');
  121. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (1,2,1985,'Gibson, William','Neuromancer','');
  122. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2013,'Leckie, Ann','Ancillary Justice','');
  123. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2012,'Robinson, Kim Stanley','2312','');
  124. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2011,'Walton, Jo','Among Others','');
  125. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2010,'Willis, Connie','Blackout/All Clear','');
  126. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2009,'Bacigalupi, Paolo','The Windup Girl','');
  127. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2008,'Le Guin, Ursula K','Powers','');
  128. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2007,'Chabon, Michael','The Yiddish Policeman''s Union','');
  129. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2006,'McDevitt, Jack','Seeker','');
  130. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2005,'Haldeman, Joe','Camouflage','');
  131. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2004,'Bujold, Lois McMaster','Paladin of Souls','');
  132. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2003,'Gaiman, Neil','American Gods','');
  133. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2002,'Asaro, Catherine','The Quantum Rose','');
  134. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2001,'Bear, Greg','Darwin''s Radio','');
  135. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,2000,'Butler, Octavia E','Parable of the Talents','');
  136. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1999,'Haldeman, Joe','Forever Peace','');
  137. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1998,'McIntyre, Vonda N','The Moon and the Sun','');
  138. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1997,'Griffith, Nicola','Slow River','');
  139. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1996,'Sawyer, Robert J','The Terminal Experiment','');
  140. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1995,'Bear, Greg','Moving Mars','');
  141. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1994,'Robinson, Kim Stanley','Red Mars','');
  142. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1993,'Willis, Connie','Doomsday Book','');
  143. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1992,'Swanwick, Michael','Stations of the Tide','');
  144. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1991,'Le Guin, Ursula K','Tehanu: The Last Book of Earthsea','');
  145. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1990,'Scarborough, Elizabeth Ann','The Healer''s War','');
  146. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1989,'Murphy, Pat','The Falling Woman','');
  147. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1988,'Card, Orson Scott','Speaker for the Dead','');
  148. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1987,'Card, Orson Scott','Ender''s Game','');
  149. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1986,'Gibson, William','Neuromancer','');
  150. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1985,'Brin, David','Startide Rising','');
  151. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (2,2,1984,'Bishop, Michael','No Enemy But Time','');
  152. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2014,'Tartt, Donna','The Goldfinch','Novel catagory renamed in 1947');
  153. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2013,'Johnson, Adam','The Orphan Master''s Son','');
  154. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2012,'n/a','n/a','no award');
  155. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2011,'Egan, Jennifer','A Visit from the Goon','');
  156. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2010,'Harding, Paul','Tinkers','');
  157. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2009,'Strout, Elizabeth','Olive Kitteridge','');
  158. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2008,'Diaz, Junot','The Brief Wonderous Life of Oscar Wao','');
  159. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2007,'McCarthy, Cormac','The Road','');
  160. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2006,'Brooks, Geraldine','March','');
  161. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2005,'Robinson, Marilynne','Gilead','');
  162. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2004,'Jones, Edward P','The Known World','');
  163. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2003,'Eugenides, Jeffrey','Middlesex','');
  164. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2002,'Russo, Richard','Empire Falls','');
  165. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2001,'Chabon, Michael','The Amazing Adventures of Kavalier & Clay','');
  166. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,2000,'Lahiri, Jhumpa','Interpreter of Maladies','');
  167. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1999,'Cunningham, Michael','The Hours','');
  168. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1998,'Roth, Philip','American Pastoral','');
  169. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1997,'Millhauser, Steven','Martin Dressler: The Tale of an American Dreamer','');
  170. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1996,'Ford, Richard','Independence Day','');
  171. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1995,'Shields, Carol','The Stone Diaries','');
  172. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1994,'Proulx, Annie','The Shipping News','');
  173. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1993,'Butler, Robert Olen','A Good Scent from a Strange Mountain','');
  174. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1992,'Smiley, Jane','A Thousand Acres','');
  175. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1991,'Updike, John','Rabbit at Rest','');
  176. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1990,'Hijuelos, Oscar','The Mambo Kings','');
  177. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1989,'Tyler, Anne','Breathing Lessongs','');
  178. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1988,'Morrison, Toni','Beloved','');
  179. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1987,'Taylor, Peter','A Summons to Memphis','');
  180. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1986,'McMurtry, Larry','Lonesome Dove','');
  181. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (3,1,1985,'Lurie, Alison','Foreign Affairs','');
  182. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2015,'Alexander, Kwame','The Crossover','');
  183. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2014,'DiCamillo, Kate','Flora & Ulysses: The Illuminated Adventures','');
  184. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2013,'Applegate, Katherine','The One and Only Ivan','');
  185. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2012,'Gantos, Jack','Dead End in Norvelt','');
  186. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2011,'Vanderpool, Clare','Moon over Manifest','');
  187. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2010,'Stead, Rebecca','When You Reach Me','');
  188. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2009,'Gaiman, Neil','The Graveyard Book','');
  189. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2008,'Schlitz, Laura Amy','Good Masters! Sweet Ladies! Voices from a Medieval Village','');
  190. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2007,'Patron, Susan','The Higher Power of Lucky','');
  191. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2006,'Perkins, Lynne Rae','Criss Cross','');
  192. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2005,'Kadohata, Cynthia','Kira-Kira','');
  193. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2004,'DiCamillo, Kate','The Tale of Despereaux: Being the Story of a Mouse, a Princess, Some Soup, and a Spool of Thread','');
  194. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2003,'Avi','Crispin: The Cross of Lead','');
  195. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2002,'Park, Linda Sue','A Single Shard','');
  196. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2001,'Peck, Richard','A Year Down Yonder','');
  197. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,2000,'Curtis, Christopher Paul','Bud, Not Buddy','');
  198. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1999,'Sachar, Louis','Holes','');
  199. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1998,'Hesse, Karen','Out of the Dust','');
  200. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1997,'Konigsburn, E.L.','The View from Saturday','');
  201. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1996,'Cushman, Kare','The Midwife''s Apprentice','');
  202. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1995,'Creech, Sharon','Walk Two Moons','');
  203. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1994,'Lowry, Lois','The Giver','');
  204. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1993,'Rylant, Cynthia','Missing May','');
  205. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1992,'Naylor, Phyllis Reynolds','Shiloh','');
  206. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1991,'Spinelli, Jerry','Maniac Magee','');
  207. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1990,'Lowry, Lois','Number the Stars','');
  208. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1989,'Fleischman, Paul','Joyful NoiseL Poems for Two Voices','');
  209. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1988,'Freedman, Russell','Lincoln: A Photobiography','');
  210. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1987,'Fleischman, Sid','The Whipping Boy','');
  211. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (5,NULL,1986,'MacLachlan, Patricia','Sarah, Plain and Tall','');
  212. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2015,'Santat, Dan','The Adventures of Beekly: The Unimaginary Friend','');
  213. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2014,'Floca, Brian','Locomotive','');
  214. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2013,'Klassen, Jon','This is Not My Hat','');
  215. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2012,'Raschka, Chris','A Ball for Daisy','');
  216. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2011,'Stead, Philip C','A Sick Day for Amos McGee','');
  217. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2010,'Pinkney, Jerry','The Lion & the Mouse','');
  218. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2009,'Swanson, Susan Marie','The House in the Night','');
  219. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2008,'Selznick, Brian','The Invention of Hugo Cabret','');
  220. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2007,'Wiesner, David','Flotsam','');
  221. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2006,'Juster, Norton','The Hello, Goodbye Window','');
  222. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2005,'Henkes, Kevin','Kitten''s First Full Moon','');
  223. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2004,'Gerstein, Mordicai','The Man Who Walked Between the Towers','');
  224. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2003,'Rohmann, Eric','My Friend Rabbit','');
  225. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2002,'Wiesner, David','The Three Pigs','');
  226. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2001,'St. George, Judith','So You Want to Be President?','');
  227. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,2000,'Taback, Simms','Joseph Had a Little Overcoat','');
  228. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1999,'Martin, Jacqueline Briggs','Snowflake Bentley','');
  229. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1998,'Zelinsky, Paul O.','Rapunzel','');
  230. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1997,'Wisniewski, David','Golem','');
  231. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1996,'Rathmann, Peggy','Officer Buckle and Gloria','');
  232. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1995,'Bunting, Eve','Smokey Night','');
  233. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1994,'Lorraine, Walter','Grandfather''s Journey','');
  234. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1993,'McCully, Emily Arnold','Mirette on the High Wire','');
  235. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1992,'Wiesner, David','Tuesday','');
  236. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1991,'Macaulay, David','Black and White','');
  237. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1990,'Young, Ed','Lon Po Po: A Red-Riding Hood Story from China','');
  238. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1989,'Ackerman, Karen','Song and Dance Man','');
  239. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1988,'Yolen, Jane','Owl Moon','');
  240. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1987,'Yorkinks, Arthur','Hey, Al','');
  241. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (6,NULL,1986,'Allsburg, Chris Van','The Polar Express','');
  242. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2014,'Modiano, Patrick','','');
  243. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2013,'Munro, Alice','','');
  244. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2012,'Yan, Mo','','');
  245. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2011,'Transtromer, Tomas','','');
  246. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2010,'Llosa, Mario Vargas','','');
  247. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2009,'Muller, Herta','','');
  248. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2008,'Le Clezio, J.M.G.','','');
  249. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2007,'Lessing, Doris','','');
  250. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2006,'Pamuk, Orhan','','');
  251. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2005,'Pinter, Harold','','');
  252. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2004,'Jelinek, Elfriede','','');
  253. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2003,'Coetzee, John M.','','');
  254. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2002,'Kertesz, Imre','','');
  255. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2001,'Naipaul, Sir Vidiadhar Surajprasad','','');
  256. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,2000,'Xingjian, Gai','','');
  257. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1999,'Grass, Gunter','','');
  258. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1998,'Saramago, Jose','','');
  259. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1997,'Fo, Dario','','');
  260. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1996,'Szymborska, Wislawa','','');
  261. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1995,'Heaney, Seamus','','');
  262. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1994,'Oe, Kenzaburo','','');
  263. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1993,'Morrison, Toni','','');
  264. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1992,'Walcott, Derek','','');
  265. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1991,'Gordimer, Nadine','','');
  266. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1990,'Paz, Octavio','','');
  267. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1989,'Cela, Camilo Jose','','');
  268. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1988,'Mahfouz, Naguib','','');
  269. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1987,'Brodsky, Joseph','','');
  270. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1986,'Soyinka, Wole','','');
  271. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (7,3,1985,'Simon, Claude','','');
  272. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2014,'Flanagan, Richard','The Narrow Road to the Deep North','');
  273. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2013,'Catton, Eleanor','The Luminaries','');
  274. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2012,'Mantel, Hilary','Bring Up the Bodies','');
  275. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2011,'Barnes, Julian','The Sense of an Ending','');
  276. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2010,'Jacobson, Howard','The Finkler Question','');
  277. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2009,'Mantel, Hilary','Wolf Hall','');
  278. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2008,'Adiga, Aravind','The White Tiger','');
  279. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2007,'Enright, Anne','The Gathering','');
  280. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2006,'Desai, Kiran','The Inheritance of Loss','');
  281. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2005,'Banville, John','The Sea','');
  282. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2004,'Hollinghurst, Alan','The Line of Beauty','');
  283. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2003,'Pierre, DBC','Vernon God Little','');
  284. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2002,'Martel, Yann','Life of Pi','');
  285. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2001,'Carey, Peter','True History of the Kelly Gang','');
  286. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,2000,'Atwood, Margaret','The Blind Assassin','');
  287. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1999,'Coetzee, John M.','Disgrace','');
  288. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1998,'McEwan, Ian','Amsterdam','');
  289. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1997,'Roy, Arundhati','The God of Small Things','');
  290. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1996,'Swift, Graham','Last Orders','');
  291. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1995,'Barker, Pat','The Ghost Road','');
  292. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1994,'Kelman, James','How Late It Was, How Late','');
  293. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1993,'Doyle, Roddy','Paddy Clarke Ha Ha Ha','');
  294. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1992,'Unsworth, Barrry','Sacred Hunger','tie');
  295. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1992,'Ondaatje, Michael','The Egnlish Patient','tie');
  296. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1991,'Okri, Ben','The Famished Road','');
  297. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1990,'Byatt, A.S.','Posession','');
  298. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1989,'Ishiguro, Kazuo','The Remains of the Day','');
  299. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1988,'Carey, Peter','Oscar and Lucinda','');
  300. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1987,'Lively, Peneloper','Moon Tiger','');
  301. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1986,'Amis, Kingsley','The Old Devils','');
  302. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,NULL,1985,'Hulme, Keri','The Bone People','');
  303. insert into extend_reporter.awards (agency,category,year,author,title,notes) values (4,4,1993,'Rushdie, Salman','Midnight''s Children','');
  304.  
  305.  
  306. select era.year, era.author, era.title, era.notes, cat.category, agency.agency,
  307. array_agg(aou.shortname) as reporterstuff
  308. from extend_reporter.awards era
  309. join extend_reporter.awards_agency agency on agency.id = era.agency
  310. left join extend_reporter.awards_category cat on cat.id = era.category
  311. join reporter.super_simple_record ssr on ssr.author = era.author
  312. join asset.call_number acn on acn.id = ssr.id
  313. join asset.copy ac on ac.call_number = acn.id
  314. join actor.org_unit aou on aou.id = ac.circ_lib
  315. where agency.agency ilike '%hugo%' and ssr.title = era.title
  316. group by 1, 2, 3, 4, 5, 6
  317. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!