If you import the attached '_mcs_book_awards' table into your metadata.db, you will be able to use the SQL Query Tab of MCS to identify matching books. You then can then, for example, add a Tag to those books with the name of the Book Award.
I recommend using the Firefox SQL Manager to import the SQL file that is attached, which will also create the table.
There is also a CSV file if you prefer to use that, although you will have to manually create the table with this SQL:
Quote:
|
CREATE TABLE "_mcs_book_awards" (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , author TEXT NOT NULL , title TEXT NOT NULL , award TEXT NOT NULL, UNIQUE(author,title,award) )
|
Example SQL to use within the SQL Query Tab of MCS:
Quote:
|
SELECT book FROM _mcs_authors_by_book WHERE authorname IN (SELECT author FROM _mcs_book_awards) AND book IN (SELECT id FROM books WHERE id = book AND title IN (SELECT title FROM _mcs_book_awards WHERE award LIKE '%%' ))
|
The '%%' will select all books in your library matching the table. To narrow the search to, for example, the Hugo Award, simply change the '%%' to '%hugo%' in order to select all books that have been awarded the Hugo Award. Then, you can add a Tag of "Hugo Award" if you wish.
Obviously, your Author Names and Book Titles have to exactly match the table, which was populated from Wikipedia, GoodReads, and other sources. For that reason, you should probably use this SQL that is a little slower than the above, although optimized for speed:
Quote:
|
SELECT book FROM _mcs_authors_by_book,_mcs_book_awards WHERE award LIKE '%hugo%' AND substr(authorname,1,3) = substr(author,1,3) AND SIMILARTO(authorname,author) > 0.80 AND _mcs_authors_by_book.book IN ( SELECT id FROM books WHERE books.id = _mcs_authors_by_book.book AND SIMILARTO(books.title,_mcs_book_awards.title) > 0.80)
|
The "SIMILARTO" function used above in "SIMILARTO(authorname,author) > 0.80" is custom to MCS, and you will not find it in the SQLite documentation. It only works for textual columns, obviously. Also, table _mcs_authors_by_book is an MCS Search Accelerator table. Its columns are 'book' and 'authorname'.
n.b. There is also an MCS Search Accelerator table for Tags, _mcs_tags_by_book, which you can use for other purposes. Its columns are 'book' and 'tagname'.
DaltonST