SQL is one of those things I always need to understand more than I do. Sometimes odd little problems can seem impossible with my limited understanding of the language.<\/p>\n
One such hit this me this morning. We wanted a report from our Institutional Repository<\/a> (a online archive of the University’s research publications). The report was to include title, journal title, type (journal article, book, etc), author(s) and departments for each item in the report.<\/p>\n Anyone with a basic understanding of relational databases will be able to picture the main table of publications, with another table for publication authors – one record for each author of each publication, with the publication’s id number to link them to the item in question<\/a>. The same set up for the departments associated with a particular item.<\/p>\n With basic SQL, it would be easy to return a list which included multiple entries for items with multi authors, i.e. an item with three authors would be repeated three times in the list, each showing a different author. An item with three authors and two departments would be repeated six times to cover all combinations of authors and departments. Not very desirable. We could also provide a list which only shows each item once (as required) but only show one author and department (ie by using GROUP BY and perhaps a function to select the first author). Again would be less than ideal.<\/p>\n A bit of googling showed creating an agregate function in postgress<\/a> (we are using mysql), and this solution which seems to use a tempory table<\/a>.<\/p>\n